Create a postgres trigger on new row with specific value in column-CodePudding

I have a table, replies and I would like to update a specific row in posts when replies has a new row inserted with a foreign key from posts.

Here is what I've got so far:

-- Inserts a row into public.users
create or replace function public.handle_updated_at() 
returns trigger as $$
begin
  update posts set updated_at = now() where postid = (postid) 
  -- not sure what goes here ^, postid comes from public.replies as a foreign key of posts

  return new;
end;
$$ language plpgsql security definer;

-- Trigger the function every time a user is created
create trigger on_new_reply
  after insert on public.replies
  for each row execute procedure public.handle_updated_at();

I'm not sure what the correct approach to this is as I haven't had much experience with SQL related things and I find the Postgres documentation to be quite difficult to understand.

My question remains, what should my trigger/function/both look like in order for this work as proposed above?

CodePudding user response：

You have not posted you table definitions so I will assume that postid is unique (or PK) posts table and a FK in replies. As your function currently stands it updates every row in the posts table. The extra parenthesis in the where clause mean nothing to Postgres. So where postid = (postid) is exactly the same as where postid = postid. Which is true for every row in the table or the same as no where clause. To get just the postid row being updated you reference new.postid. So your trigger function becomes:

-- Inserts a row into public.users
create or replace function public.handle_updated_at() 
returns trigger as $$
begin
  update posts 
     set updated_at = now() 
   where postid = new.postid;
  return new;
end;
$$ language plpgsql security definer;

Your trigger itself is fine.