migrations/2023-07-27-134652_remove-expensive-broken-trigger/up.sql

   1 create or replace function person_aggregates_comment_count()
   2     returns trigger language plpgsql
   3 as $$
   4 begin
   5     IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
   6         update person_aggregates
   7         set comment_count = comment_count + 1 where person_id = NEW.creator_id;
   8     ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
   9         update person_aggregates
  10         set comment_count = comment_count - 1 where person_id = OLD.creator_id;
  11     END IF;
  12     return null;
  13 end $$;
  14
  15 create or replace function person_aggregates_post_count()
  16     returns trigger language plpgsql
  17 as $$
  18 begin
  19     IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
  20         update person_aggregates
  21         set post_count = post_count + 1 where person_id = NEW.creator_id;
  22
  23     ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
  24         update person_aggregates
  25         set post_count = post_count - 1 where person_id = OLD.creator_id;
  26     END IF;
  27     return null;
  28 end $$;
  29
  30 create or replace function community_aggregates_comment_count()
  31     returns trigger language plpgsql
  32 as $$
  33 begin
  34   IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
  35 update community_aggregates ca
  36 set comments = comments + 1 from post p
  37 where p.id = NEW.post_id
  38   and ca.community_id = p.community_id;
  39 ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
  40 update community_aggregates ca
  41 set comments = comments - 1 from post p
  42 where p.id = OLD.post_id
  43   and ca.community_id = p.community_id;
  44
  45 END IF;
  46 return null;
  47 end $$;