1 -- Update comment_aggregates_score trigger function to exclude controversy_rank update
2 create or replace function comment_aggregates_score()
3 returns trigger language plpgsql
6 IF (TG_OP = 'INSERT') THEN
7 update comment_aggregates ca
8 set score = score + NEW.score,
9 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
10 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
11 where ca.comment_id = NEW.comment_id;
13 ELSIF (TG_OP = 'DELETE') THEN
14 -- Join to comment because that comment may not exist anymore
15 update comment_aggregates ca
16 set score = score - OLD.score,
17 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
18 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
20 where ca.comment_id = c.id
21 and ca.comment_id = OLD.comment_id;
27 -- Update post_aggregates_score trigger function to exclude controversy_rank update
28 create or replace function post_aggregates_score()
29 returns trigger language plpgsql
32 IF (TG_OP = 'INSERT') THEN
33 update post_aggregates pa
34 set score = score + NEW.score,
35 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
36 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
37 where pa.post_id = NEW.post_id;
39 ELSIF (TG_OP = 'DELETE') THEN
40 -- Join to post because that post may not exist anymore
41 update post_aggregates pa
42 set score = score - OLD.score,
43 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
44 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
46 where pa.post_id = p.id
47 and pa.post_id = OLD.post_id;
53 drop index if exists idx_post_aggregates_featured_local_controversy;
54 drop index if exists idx_post_aggregates_featured_community_controversy;
55 drop index if exists idx_comment_aggregates_controversy;
57 -- Remove the added columns from the tables
58 alter table post_aggregates drop column controversy_rank;
59 alter table comment_aggregates drop column controversy_rank;
62 drop function controversy_rank(numeric, numeric);