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,
8 published timestamp not null default now(),
12 insert into comment_aggregates (comment_id, score, upvotes, downvotes, published)
15 COALESCE(cl.total, 0::bigint) AS score,
16 COALESCE(cl.up, 0::bigint) AS upvotes,
17 COALESCE(cl.down, 0::bigint) AS downvotes,
20 left join ( select l.comment_id as id,
21 sum(l.score) as total,
24 when l.score = 1 then 1
29 when l.score = '-1'::integer then 1
33 group by l.comment_id) cl on cl.id = c.id;
35 -- Add comment aggregate triggers
37 -- initial comment add
38 create function comment_aggregates_comment()
39 returns trigger language plpgsql
42 IF (TG_OP = 'INSERT') THEN
43 insert into comment_aggregates (comment_id) values (NEW.id);
44 ELSIF (TG_OP = 'DELETE') THEN
45 delete from comment_aggregates where comment_id = OLD.id;
50 create trigger comment_aggregates_comment
51 after insert or delete on comment
53 execute procedure comment_aggregates_comment();
56 create function comment_aggregates_score()
57 returns trigger language plpgsql
60 IF (TG_OP = 'INSERT') THEN
61 update comment_aggregates ca
62 set score = score + NEW.score,
63 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
64 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
65 where ca.comment_id = NEW.comment_id;
67 ELSIF (TG_OP = 'DELETE') THEN
68 -- Join to comment because that comment may not exist anymore
69 update comment_aggregates ca
70 set score = score - OLD.score,
71 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
72 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
74 where ca.comment_id = c.id
75 and ca.comment_id = OLD.comment_id;
81 create trigger comment_aggregates_score
82 after insert or delete on comment_like
84 execute procedure comment_aggregates_score();