Supabase triggers : Getting syntax error at or near "select" (SQL)-CodePudding

Iam trying to insert a new row in profiles table when a new user is created in auth table using a trigger in the supabase dashboard

But when creating the function for the trigger I get Failed to create function: failed to update pg.functions with the given ID: syntax error at or near "select" error

This is the function that is called in the trigger

begin
  insert into public.profiles (id,email,user_name)
  values (new.id, new.email, select left(replace(new.email, '.', '-'), charindex('@', replace(new.email, '.', '-')) - 1));
  return new;
end;

The profiles table has 3 columns , id,email,user_name

the user_name needs to be characters before "@" in an email , and any "." needs to be replaced with "-" , example : "[email protected]" -> "test-123"

Screen shot for the created function in supabase

Iam new to SQL and having trouble figuring this out , any help is must appreciated !

CodePudding user response：

Try phrasing using an INSERT INTO ... SELECT:

begin
    insert into public.profiles (id, email, user_name)
    select new.id, new.email,
           left(replace(new.email, '.', '-'), charindex('@', replace(new.email, '.', '-')) - 1));
return new;
end;