https://supabase.com/ logo
Join Discord
Powered by
Found this example for copying the id on every new...
# sql
e
Found this example for copying the id on every new creation of a user. But how do I update values on update of a user?
Copy code
sql
create function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();
g
Please comment in this thread
You can create a function that is triggered on update rather than insert
And either just update last sign in only, or do a quick check and only update if it changed
Without thinking about what all updates the auth.user row, it is best to just check for what you want.
e
I see, but I am not sure how to write it yet, this language is foreign to me
what does 
new
represent?
do I need to use it on update?
g
new is the row inserted with . used for the columns. old is the data before the insert. This is on update. Insert just has new
e
oh I see, so on 
update
I also have 
old
?
g
So without learning how to do an if statement in plpgsql you could just add a trigger function like above except and just change the code in the function from insert to your public table to update (slightly different sql format) and use new.last_sign_in_at.
Then if you want to figure out if statements you can do an if statement and compare old.blah to new.blah and do a then.
yes update has and old variable.
e
Maybe something like this?
Copy code
sql
create function public.handle_updated_user() 
returns trigger as $$
begin
  set public.users (last_sign_in_at)
  values (new.last_sign_in_at)
  where new.last_sign_in_at > old.last_sign_in_at;
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_updated_user();
g
No for first run just drop the where.
do it every time then figure out the if then if you want.
You can actually do both insert and update in a single function as postgres has a variable you can see if insert/update/delete on a multi trigger type call.
e
I guess could be like this.
Copy code
sql
create function public.handle_updated_user() 
returns trigger as $$
if old.last_sign_in_at != new.last_sign_in_at then
  update public.public.user_details
  set public.users (last_sign_in_at)
  where id = new.id;
end if;

$$ language plpgsql security definer;
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_updated_user();
Looked at that thread:) trying to thread things up...
g
Looks correct at a quick glance. I would search here and in github discussions if you need more ideas. It has all been done
e
that's a good idea, I should use search more 😄
I have the initial function I use now for creating new user:
Copy code
sql
create or replace function public.handle_new_signup() 
returns trigger as $$
begin
  insert into public.user_details (id, email, created_at)
  values
  (
    new.id,
    new.email,
    new.created_at
  );
  return new;
end;

$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_signup();
I guess u could update it as you said
Hmm tried like this but doesn't work....
Copy code
sql
create or replace function public.handle_new_signup() 
returns trigger as $$

if id is null then
  insert into public.user_details (id, email, created_at)
  values
  (
    new.id,
    new.email,
    new.created_at
  );
end if;

if old.last_sign_in_at != new.last_sign_in_at then
  update public.public.user_details
  set public.users (last_sign_in_at)
  where id = new.id;
end if;

$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert or update on auth.users
  for each row execute procedure public.handle_new_signup();
Failed to run sql query: syntax error at or near "if"
g
you're missing begin
Also if you want to do a combined function you need see https://www.postgresql.org/docs/current/plpgsql-trigger.html and tg_op
e
Oh I see. Thanks! But I just figure out something... 
auth.last_sign_in_at
tracks the actual log in. User can spend days or weeks on a session without logging in. This is not what I want to track. Not sure if this will even be possible in Postgresql, it's not actually clear what to track.
PreviousNext
Powered by