Trigger and function to insert user id into another table-CodePudding

I am using Prisma as my schema and migrating it to supabase with prisma migrate dev

One of my tables Profiles, should reference the auth.users table in supabase, in sql something like this id uuid references auth.users not null,

Now since that table is automatically created in supabase do I still add it to my prisma schema? It's not in public either it is in auth.

model Profiles {
  id               String   @id @db.Uuid
  role             String
  subId            String
  stripeCustomerId String
  refundId         String[]
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt
}

The reason I want the relation is because I want a trigger to automatically run a function that inserts an id and role into the profiles table when a new users is invited.

This is that trigger and function

-- inserts a row into public.profiles
create function public.handle_new_user() 
returns trigger 
language plpgsql 
security definer 
as $$
begin
  insert into public.Profiles (id, role)
  values (new.id, 'BASE_USER');
  return new;
end;
$$;

-- 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();

I had this working when I created the profiles table manually in supabase I included the reference to the auth.users, that's the only reason I can think of why the user Id and role won't insert into the profiles db when I invite a user, the trigger and function are failing

create table public.Profiles (
  id uuid references auth.users not null,
  role text,

  primary key (id)
);

Update from comment:
One error I found is

relation "public.profiles" does not exist

I change it to "public.Profiles" with a capital in supabase, but the function seem to still be looking for lowercase.

CodePudding user response：

What you show should just work:

db<>fiddle here

Looks like you messed up capitalization with Postgres identifiers.

If you (or your ORM) created the table as "Profiles" (with double-quotes), non-standard capitalization is preserved and you need to double-quote the name for the rest of its life.

So the trigger function body must read:

...
insert into public."Profiles" (id, role)  -- with double-quotes
...

Note that schema and table (and column) have to be quoted separately.

See:

Are PostgreSQL column names case-sensitive?