migrations/2023-07-26-000217_create_controversial_indexes/down.sql

   1 -- Update comment_aggregates_score trigger function to exclude controversy_rank update
   2 CREATE OR REPLACE FUNCTION comment_aggregates_score ()
   3     RETURNS TRIGGER
   4     LANGUAGE plpgsql
   5     AS $$
   6 BEGIN
   7     IF (TG_OP = 'INSERT') THEN
   8         UPDATE
   9             comment_aggregates ca
  10         SET
  11             score = score + NEW.score,
  12             upvotes = CASE WHEN NEW.score = 1 THEN
  13                 upvotes + 1
  14             ELSE
  15                 upvotes
  16             END,
  17             downvotes = CASE WHEN NEW.score = - 1 THEN
  18                 downvotes + 1
  19             ELSE
  20                 downvotes
  21             END
  22         WHERE
  23             ca.comment_id = NEW.comment_id;
  24     ELSIF (TG_OP = 'DELETE') THEN
  25         -- Join to comment because that comment may not exist anymore
  26         UPDATE
  27             comment_aggregates ca
  28         SET
  29             score = score - OLD.score,
  30             upvotes = CASE WHEN OLD.score = 1 THEN
  31                 upvotes - 1
  32             ELSE
  33                 upvotes
  34             END,
  35             downvotes = CASE WHEN OLD.score = - 1 THEN
  36                 downvotes - 1
  37             ELSE
  38                 downvotes
  39             END
  40         FROM
  41             comment c
  42         WHERE
  43             ca.comment_id = c.id
  44             AND ca.comment_id = OLD.comment_id;
  45     END IF;
  46     RETURN NULL;
  47 END
  48 $$;
  49
  50 -- Update post_aggregates_score trigger function to exclude controversy_rank update
  51 CREATE OR REPLACE FUNCTION post_aggregates_score ()
  52     RETURNS TRIGGER
  53     LANGUAGE plpgsql
  54     AS $$
  55 BEGIN
  56     IF (TG_OP = 'INSERT') THEN
  57         UPDATE
  58             post_aggregates pa
  59         SET
  60             score = score + NEW.score,
  61             upvotes = CASE WHEN NEW.score = 1 THEN
  62                 upvotes + 1
  63             ELSE
  64                 upvotes
  65             END,
  66             downvotes = CASE WHEN NEW.score = - 1 THEN
  67                 downvotes + 1
  68             ELSE
  69                 downvotes
  70             END
  71         WHERE
  72             pa.post_id = NEW.post_id;
  73     ELSIF (TG_OP = 'DELETE') THEN
  74         -- Join to post because that post may not exist anymore
  75         UPDATE
  76             post_aggregates pa
  77         SET
  78             score = score - OLD.score,
  79             upvotes = CASE WHEN OLD.score = 1 THEN
  80                 upvotes - 1
  81             ELSE
  82                 upvotes
  83             END,
  84             downvotes = CASE WHEN OLD.score = - 1 THEN
  85                 downvotes - 1
  86             ELSE
  87                 downvotes
  88             END
  89         FROM
  90             post p
  91         WHERE
  92             pa.post_id = p.id
  93             AND pa.post_id = OLD.post_id;
  94     END IF;
  95     RETURN NULL;
  96 END
  97 $$;
  98
  99 -- Drop the indexes
 100 DROP INDEX IF EXISTS idx_post_aggregates_featured_local_controversy;
 101
 102 DROP INDEX IF EXISTS idx_post_aggregates_featured_community_controversy;
 103
 104 DROP INDEX IF EXISTS idx_comment_aggregates_controversy;
 105
 106 -- Remove the added columns from the tables
 107 ALTER TABLE post_aggregates
 108     DROP COLUMN controversy_rank;
 109
 110 ALTER TABLE comment_aggregates
 111     DROP COLUMN controversy_rank;
 112
 113 -- Remove function
 114 DROP FUNCTION controversy_rank (numeric, numeric);
 115