Nested SQL Insert in Postgre Function

Question

I have this function that is triggered, when a new user signs up in my app (supabase backen):

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
  insert into public.user_profile (id, email, full_name)
  values (new.id, new.email, new.raw_user_meta_data->>'full_name');
  return new;
end;
$function$
;

Now I want to first insert a row to another table and put the resulting id of this row inside a new column in my user_profile.

Something like this:

  id = insert into other_table (created_by, somecolumn)
  values (new.id, 100000);
  
  insert into public.user_profile (id, email, full_name, foreign_column)
  values (new.id, new.email, new.raw_user_meta_data->>'full_name', id);
  return new;

What is the correct syntax in postgreSQL to do this insertAndSelect? So that I can use it to insert the foreign_key into my user_profile afterwards?

EDIT: my table definitions

create table
  public.user_profile (
    id uuid not null,
    full_name text null,
    email text not null,
    spending_cap integer null,
    constraint profiles_pkey primary key (id),
    constraint user_profile_id_fkey foreign key (id) references auth.users (id),
    constraint user_profile_spending_cap_fkey foreign key (spending_cap) references spending_cap (id)
  ) tablespace pg_default;
  
  
create table
  public.spending_cap (
    id integer generated by default as identity,
    created_by uuid null,
    cap bigint null,
    constraint lobby_pkey primary key (id),
    constraint spending_cap_created_by_fkey foreign key (created_by) references user_profile (id)
  ) tablespace pg_default;

I took the trigger from this guide

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

The auth.user table is created by supabase and is big:

create table
  auth.users (
    instance_id uuid null,
    id uuid not null,
    aud character varying(255) null,
    role character varying(255) null,
    email character varying(255) null,
    encrypted_password character varying(255) null,
    email_confirmed_at timestamp with time zone null,
    invited_at timestamp with time zone null,
    confirmation_token character varying(255) null,
    confirmation_sent_at timestamp with time zone null,
    recovery_token character varying(255) null,
    recovery_sent_at timestamp with time zone null,
    email_change_token_new character varying(255) null,
    email_change character varying(255) null,
    email_change_sent_at timestamp with time zone null,
    last_sign_in_at timestamp with time zone null,
    raw_app_meta_data jsonb null,
    raw_user_meta_data jsonb null,
    is_super_admin boolean null,
    created_at timestamp with time zone null,
    updated_at timestamp with time zone null,
    phone text null default null::character varying,
    phone_confirmed_at timestamp with time zone null,
    phone_change text null default ''::character varying,
    phone_change_token character varying(255) null default ''::character varying,
    phone_change_sent_at timestamp with time zone null,
    confirmed_at timestamp with time zone null,
    email_change_token_current character varying(255) null default ''::character varying,
    email_change_confirm_status smallint null default 0,
    banned_until timestamp with time zone null,
    reauthentication_token character varying(255) null default ''::character varying,
    reauthentication_sent_at timestamp with time zone null,
    is_sso_user boolean not null default false,
    deleted_at timestamp with time zone null,
    constraint users_pkey primary key (id),
    constraint users_phone_key unique (phone),
    constraint users_email_change_confirm_status_check check (
      (
        (email_change_confirm_status >= 0)
        and (email_change_confirm_status <= 2)
      )
    )
  ) tablespace pg_default;

create unique index confirmation_token_idx on auth.users using btree (confirmation_token)
where
  ((confirmation_token)::text !~ '^[0-9 ]*$'::text) tablespace pg_default;

create unique index email_change_token_current_idx on auth.users using btree (email_change_token_current)
where
  (
    (email_change_token_current)::text !~ '^[0-9 ]*$'::text
  ) tablespace pg_default;

create unique index email_change_token_new_idx on auth.users using btree (email_change_token_new)
where
  (
    (email_change_token_new)::text !~ '^[0-9 ]*$'::text
  ) tablespace pg_default;

create unique index reauthentication_token_idx on auth.users using btree (reauthentication_token)
where
  (
    (reauthentication_token)::text !~ '^[0-9 ]*$'::text
  ) tablespace pg_default;

create unique index recovery_token_idx on auth.users using btree (recovery_token)
where
  ((recovery_token)::text !~ '^[0-9 ]*$'::text) tablespace pg_default;

create unique index users_email_partial_key on auth.users using btree (email)
where
  (is_sso_user = false) tablespace pg_default;

create index if not exists users_instance_id_email_idx on auth.users using btree (instance_id, lower((email)::text)) tablespace pg_default;

create index if not exists users_instance_id_idx on auth.users using btree (instance_id) tablespace pg_default;

create trigger on_auth_user_created
after insert on auth.users for each row
execute function handle_new_user ();

Answer 1

You can use RETURNING to set the value for a variable:

CREATE OR REPLACE FUNCTION public.handle_new_user()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    SECURITY DEFINER
AS
$function$
DECLARE
    _id_other_table INT;
BEGIN
    INSERT INTO other_table (created_by, somecolumn)
    VALUES (new.id, 100000)
    RETURNING id
        INTO _id_other_table;

    INSERT INTO public.user_profile (id, email, full_name, foreign_column)
    VALUES (new.id, new.email, new.raw_user_meta_data ->> 'full_name', _id_other_table);

    RETURN new;
END;
$function$;