Getting columns ON CONFLICT without a DO UPDATE

112 views Asked by At

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.

0

There are 0 answers