1 -- Add comment aggregates
2 create table comment_aggregates (
4 comment_id int references comment on update cascade on delete cascade not null,
5 score bigint not null default 0,
6 upvotes bigint not null default 0,
7 downvotes bigint not null default 0,
11 insert into comment_aggregates (comment_id, score, upvotes, downvotes)
14 COALESCE(cl.total, 0::bigint) AS score,
15 COALESCE(cl.up, 0::bigint) AS upvotes,
16 COALESCE(cl.down, 0::bigint) AS downvotes
18 left join ( select l.comment_id as id,
19 sum(l.score) as total,
22 when l.score = 1 then 1
27 when l.score = '-1'::integer then 1
31 group by l.comment_id) cl on cl.id = c.id;
33 -- Add comment aggregate triggers
35 -- initial comment add
36 create function comment_aggregates_comment()
37 returns trigger language plpgsql
40 IF (TG_OP = 'INSERT') THEN
41 insert into comment_aggregates (comment_id) values (NEW.id);
42 ELSIF (TG_OP = 'DELETE') THEN
43 delete from comment_aggregates where comment_id = OLD.id;
48 create trigger comment_aggregates_comment
49 after insert or delete on comment
51 execute procedure comment_aggregates_comment();
54 create function comment_aggregates_score()
55 returns trigger language plpgsql
58 IF (TG_OP = 'INSERT') THEN
59 update comment_aggregates ca
60 set score = score + NEW.score,
61 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
62 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
63 where ca.comment_id = NEW.comment_id;
65 ELSIF (TG_OP = 'DELETE') THEN
66 -- Join to comment because that comment may not exist anymore
67 update comment_aggregates ca
68 set score = score - OLD.score,
69 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
70 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
72 where ca.comment_id = c.id
73 and ca.comment_id = OLD.comment_id;
79 create trigger comment_aggregates_score
80 after insert or delete on comment_like
82 execute procedure comment_aggregates_score();