Created
December 29, 2016 01:41
-
-
Save shicholas/74c43efc1bf283b0749c9e33ec369102 to your computer and use it in GitHub Desktop.
SQL function for finding/creating a person based on an invited user attribute
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE OR REPLACE FUNCTION find_or_create_person(invited_user_id uuid) | |
| RETURNS SETOF people AS | |
| $BODY$ | |
| DECLARE | |
| invited_user invited_users%ROWTYPE; | |
| person people%ROWTYPE; | |
| BEGIN | |
| RAISE NOTICE 'Verifying the invited user exists'; | |
| SELECT INTO invited_user * | |
| FROM invited_users u | |
| WHERE u.id = $1; | |
| IF invited_user is null THEN | |
| RAISE EXCEPTION 'no invited user found'; | |
| END IF; | |
| RAISE NOTICE 'Trying to find a person with the invited user email'; | |
| SELECT INTO person * | |
| FROM people p | |
| WHERE p.email = invited_user.email | |
| AND p.name = invited_user.name; | |
| IF person is null THEN | |
| RAISE NOTICE 'Creating a person because one did not exist'; | |
| INSERT INTO PEOPLE ( | |
| name, | |
| email, | |
| created_at, | |
| updated_at | |
| ) VALUES ( | |
| invited_user.name, | |
| invited_user.email, | |
| CURRENT_TIMESTAMP, | |
| CURRENT_TIMESTAMP | |
| ) returning INTO person *; | |
| RETURN NEXT person; | |
| ELSE | |
| RAISE NOTICE 'Returning person because it did exist'; | |
| RETURN NEXT person; | |
| END IF; | |
| RETURN; | |
| END; | |
| $BODY$ LANGUAGE 'plpgsql'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment