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

   1 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
   2     RETURNS TRIGGER
   3     LANGUAGE plpgsql
   4     AS $$
   5 BEGIN
   6     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
   7         UPDATE
   8             person_aggregates
   9         SET
  10             comment_count = comment_count + 1
  11         WHERE
  12             person_id = NEW.creator_id;
  13     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  14         UPDATE
  15             person_aggregates
  16         SET
  17             comment_count = comment_count - 1
  18         WHERE
  19             person_id = OLD.creator_id;
  20     END IF;
  21     RETURN NULL;
  22 END
  23 $$;
  24
  25 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
  26     RETURNS TRIGGER
  27     LANGUAGE plpgsql
  28     AS $$
  29 BEGIN
  30     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  31         UPDATE
  32             person_aggregates
  33         SET
  34             post_count = post_count + 1
  35         WHERE
  36             person_id = NEW.creator_id;
  37     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  38         UPDATE
  39             person_aggregates
  40         SET
  41             post_count = post_count - 1
  42         WHERE
  43             person_id = OLD.creator_id;
  44     END IF;
  45     RETURN NULL;
  46 END
  47 $$;
  48
  49 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
  50     RETURNS TRIGGER
  51     LANGUAGE plpgsql
  52     AS $$
  53 BEGIN
  54     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  55         UPDATE
  56             community_aggregates ca
  57         SET
  58             comments = comments + 1
  59         FROM
  60             post p
  61         WHERE
  62             p.id = NEW.post_id
  63             AND ca.community_id = p.community_id;
  64     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  65         UPDATE
  66             community_aggregates ca
  67         SET
  68             comments = comments - 1
  69         FROM
  70             post p
  71         WHERE
  72             p.id = OLD.post_id
  73             AND ca.community_id = p.community_id;
  74     END IF;
  75     RETURN NULL;
  76 END
  77 $$;
  78