Trying to come up with best performing approach:
Option 1: using INSERT ON CONFLICT DO UPDATE
CREATE OR REPLACE FUNCTION public.start_gmail_user_session(
input json, OUT user_session json)
LANGUAGE plpgsql
AS $BODY$
DECLARE
input_email varchar(80) := LOWER(TRIM((input->>'email')::varchar));
input_first_name varchar(20) := TRIM((input->>'firstName')::varchar);
input_last_name varchar(20) := TRIM((input->>'lastName')::varchar);
input_phone varchar(23) := TRIM((input->>'phone')::varchar);
BEGIN
INSERT INTO users (role, email, first_name, last_name, phone)
VALUES ('student', input_email, input_first_name, input_last_name, input_phone)
ON CONFLICT (email) DO UPDATE SET email = input_email
RETURNING json_build_object('id', create_session(id), 'user', json_build_object('id', id, 'role', role, 'email', input_email, 'firstName', first_name, 'lastName', last_name, 'phone', phone)) INTO user_session;
END;
$BODY$;
Seemed to me that the DO UPDATE SET email = input_email just so I could get values from RETURNING isn't efficient.
Option 2: using FOUND
CREATE OR REPLACE FUNCTION public.start_gmail_user_session(input json, OUT user_session json)
LANGUAGE plpgsql
AS $BODY$
DECLARE
input_email varchar(80) := LOWER(TRIM((input->>'email')::varchar));
input_first_name varchar(20) := TRIM((input->>'firstName')::varchar);
input_last_name varchar(20) := TRIM((input->>'lastName')::varchar);
input_phone varchar(23) := TRIM((input->>'phone')::varchar);
BEGIN
SELECT json_build_object('id', create_session(users.id), 'user', json_build_object('id', users.id, 'role', users.role, 'email', input_email, 'firstName', users.first_name, 'lastName', users.last_name, 'phone', users.phone)) INTO user_session FROM users WHERE email = input_email;
IF NOT FOUND THEN
INSERT INTO users(role, email, first_name, last_name, phone)
VALUES('student', input_email, input_first_name, input_last_name, input_phone)
RETURNING json_build_object('id', create_session(id), 'user', json_build_object('id', id, 'role', 'student', 'email', input_email, 'firstName', input_first_name, 'lastName', input_last_name, 'phone', input_phone)) INTO user_session;
END IF;
END;
$BODY$;
Option 1 was a little slower.
Is there a better way to do option 1 where I could use DO NOTHING instead of a bogus DO UPDATE SET email = input_email? The latter seemed to be the only way for the RETURNING to work.