migrations/2021-02-10-164051_add_new_comments_sort_index/down.sql

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