migrations/2021-08-02-002342_comment_count_fixes/down.sql

   1 DROP TRIGGER post_aggregates_comment_set_deleted ON comment;
   2
   3 DROP FUNCTION post_aggregates_comment_deleted;
   4
   5 CREATE OR REPLACE FUNCTION post_aggregates_comment_count ()
   6     RETURNS TRIGGER
   7     LANGUAGE plpgsql
   8     AS $$
   9 BEGIN
  10     IF (TG_OP = 'INSERT') THEN
  11         UPDATE
  12             post_aggregates pa
  13         SET
  14             comments = comments + 1,
  15             newest_comment_time = NEW.published
  16         WHERE
  17             pa.post_id = NEW.post_id;
  18         -- A 2 day necro-bump limit
  19         UPDATE
  20             post_aggregates pa
  21         SET
  22             newest_comment_time_necro = NEW.published
  23         WHERE
  24             pa.post_id = NEW.post_id
  25             AND published > ('now'::timestamp - '2 days'::interval);
  26     ELSIF (TG_OP = 'DELETE') THEN
  27         -- Join to post because that post may not exist anymore
  28         UPDATE
  29             post_aggregates pa
  30         SET
  31             comments = comments - 1
  32         FROM
  33             post p
  34         WHERE
  35             pa.post_id = p.id
  36             AND pa.post_id = OLD.post_id;
  37     END IF;
  38     RETURN NULL;
  39 END
  40 $$;
  41