Find or Create Person based of a related "Invited User" record. Email and Name in both records should match before creating a new one.

find-or-create-person.sql

-- :name define-find-or-create-person :!
-- :doc define find-or-create-person function based off invited user id
CREATE OR REPLACE FUNCTION find_or_create_person(invited_user_id UUID)
RETURNS record AS
$BODY$
DECLARE
  invited_user record;
  person record;
BEGIN
  RAISE NOTICE 'Verifying the invited user exists';
  SELECT INTO invited_user *
  FROM invited_users u
  WHERE u.id = $1;

  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 *;
  ELSE
    RAISE NOTICE 'Person record already existed';
    RETURN person;
  END IF;
END;
$BODY$ LANGUAGE 'plpgsql';