Discussion on: 🐘🚀 Triggers & Stored Procedures for pure data integrity logic and performance

[Pavel Stehule]

your triggers can be faster if you use more effective array based operations instead foreach cycle - delete from where id = any (array), inser into .. select unnest(array)

[Franck Pachot]

Thanks Pavel! Yes, faster, less code and much more readable.

CREATE OR REPLACE FUNCTION posts_by_user_dml()
RETURNS TRIGGER AS
$$
begin
 -- delete entries for old values of tag_ids
  delete from posts_by_tag t
     where t.tag_id = any (old.tag_ids)
       and t.user_id= old.user_id
       and t.post_id= old.post_id;
 -- insert entries for new values of tag_ids
 insert into posts_by_tag(tag_id,user_id, post_id,created_date)
     select unnest(new.tag_ids),new.user_id, new.post_id, new.created_date;
  -- delete entries for old values of group_ids
  delete from posts_by_group t 
     where t.group_id = any (old.group_ids)
       and t.user_id= old.user_id
       and t.post_id= old.post_id;
  -- insert entries for new values of group_ids
  insert into posts_by_group(group_id,user_id, post_id,created_date)
     select unnest(new.group_ids),new.user_id, new.post_id, new.created_date;
 return new;
end;
$$
LANGUAGE plpgsql;

I removed the test on null by the same occasion as this should not be aa general case so we have the beauty of SQL only :)