How to insert result of select query (with default if return no rows) as part of Postgres Function?-CodePudding

For my Nextjs website, I am configuring the backend to automatically add user information to a public.profiles table whenever a row is inserted into the auth.users table (automatically done through Supabase's authentication).

I have a public.college_emails table that contains the names of over 1700 colleges and the email tag associated with them. When a user creates an account with their school email, I want it to return the college name associated with their email tag and enter that in their "school" field. Otherwise if they used an email not on my list, it will insert "other".

I was able to get Supabase to compile my SQL without syntax errors, but upon trying to add a new user, it fails with error Database error when creating user. (This I found out to be when there is a problem with a trigger or function associated with user creation. When I removed the code carrying out my previously mentioned feature, it worked fine (but just doesn't properly select for college name.

This is my PostgreSQL code that does not work. It has no reported syntax errors, but fails when trying to insert a user:

create table if not exists public.profiles (
id uuid not null primary key, -- UUID from auth.users
email text,
full_name text,
avatar_url text,
created_at timestamp with time zone
);
create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.profiles (id, email, full_name, school, created_at)
  values (new.id, new.email, SPLIT_PART(new.email, '@', 1), 
  (SELECT coalesce(college, 'Other') FROM college_emails WHERE tag = SPLIT_PART(new.email, '@', 2)) // THIS LINE DOES NOT WORK
  ,current_timestamp);
  return new;
end;
$$ language plpgsql security definer;

create trigger on_new_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

My table for colleges is in the format of:

email (text): '[email protected]'; college (text): 'My College'; tag (text): 'mycollege.edu'

This is what my function is now (reverted to a working version to continue testing). This lacks the code to add the correct college name. The table and trigger remain the same:

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.profiles (id, email, full_name, school, created_at)
  values (new.id, new.email, SPLIT_PART(new.email, '@', 1), SPLIT_PART(SPLIT_PART(new.email, '@', 2), '.', 1)
  ,current_timestamp);
  return new;
end;
$$ language plpgsql security definer;

My expected result:

User with email "[email protected]" registers and is added to auth.users. A new row is added to public.profiles with email: "[email protected]", username: "myname", and school: "University of California Irvine"

CodePudding user response：

Update your trigger function public.handle_new_user() like this :

create or replace function public.handle_new_user() 
returns trigger as $$
declare
  coll text ;
begin
  SELECT college
    INTO coll
    FROM college_emails 
   WHERE tag = SPLIT_PART(new.email, '@', 2);

  IF NOT FOUND
  THEN coll = 'Other' ;
  END IF ;
  
  INSERT INTO public.profiles (id, email, full_name, school, created_at)
  SELECT  new.id, new.email, SPLIT_PART(new.email, '@', 1), coll, current_timestamp ;
  return new;
end;
$$ language plpgsql security definer;