1 -- Update comment_aggregates_score trigger function to exclude controversy_rank update
2 CREATE OR REPLACE FUNCTION comment_aggregates_score ()
7 IF (TG_OP = 'INSERT') THEN
11 score = score + NEW.score,
12 upvotes = CASE WHEN NEW.score = 1 THEN
17 downvotes = CASE WHEN NEW.score = - 1 THEN
23 ca.comment_id = NEW.comment_id;
24 ELSIF (TG_OP = 'DELETE') THEN
25 -- Join to comment because that comment may not exist anymore
29 score = score - OLD.score,
30 upvotes = CASE WHEN OLD.score = 1 THEN
35 downvotes = CASE WHEN OLD.score = - 1 THEN
44 AND ca.comment_id = OLD.comment_id;
50 -- Update post_aggregates_score trigger function to exclude controversy_rank update
51 CREATE OR REPLACE FUNCTION post_aggregates_score ()
56 IF (TG_OP = 'INSERT') THEN
60 score = score + NEW.score,
61 upvotes = CASE WHEN NEW.score = 1 THEN
66 downvotes = CASE WHEN NEW.score = - 1 THEN
72 pa.post_id = NEW.post_id;
73 ELSIF (TG_OP = 'DELETE') THEN
74 -- Join to post because that post may not exist anymore
78 score = score - OLD.score,
79 upvotes = CASE WHEN OLD.score = 1 THEN
84 downvotes = CASE WHEN OLD.score = - 1 THEN
93 AND pa.post_id = OLD.post_id;
100 DROP INDEX IF EXISTS idx_post_aggregates_featured_local_controversy;
102 DROP INDEX IF EXISTS idx_post_aggregates_featured_community_controversy;
104 DROP INDEX IF EXISTS idx_comment_aggregates_controversy;
106 -- Remove the added columns from the tables
107 ALTER TABLE post_aggregates
108 DROP COLUMN controversy_rank;
110 ALTER TABLE comment_aggregates
111 DROP COLUMN controversy_rank;
114 DROP FUNCTION controversy_rank (numeric, numeric);