2 create table post_aggregates (
4 post_id int references post on update cascade on delete cascade not null,
5 comments bigint not null default 0,
6 score bigint not null default 0,
7 upvotes bigint not null default 0,
8 downvotes bigint not null default 0,
9 newest_comment_time timestamp not null default now(),
13 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, newest_comment_time)
16 coalesce(ct.comments, 0::bigint) as comments,
17 coalesce(pl.score, 0::bigint) as score,
18 coalesce(pl.upvotes, 0::bigint) as upvotes,
19 coalesce(pl.downvotes, 0::bigint) as downvotes,
20 greatest(ct.recent_comment_time, p.published) as newest_activity_time
23 select comment.post_id,
25 max(comment.published) as recent_comment_time
27 group by comment.post_id
28 ) ct on ct.post_id = p.id
30 select post_like.post_id,
31 sum(post_like.score) as score,
32 sum(post_like.score) filter (where post_like.score = 1) as upvotes,
33 -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
35 group by post_like.post_id
36 ) pl on pl.post_id = p.id;
38 -- Add community aggregate triggers
41 create function post_aggregates_post()
42 returns trigger language plpgsql
45 IF (TG_OP = 'INSERT') THEN
46 insert into post_aggregates (post_id) values (NEW.id);
47 ELSIF (TG_OP = 'DELETE') THEN
48 delete from post_aggregates where post_id = OLD.id;
53 create trigger post_aggregates_post
54 after insert or delete on post
56 execute procedure post_aggregates_post();
59 create function post_aggregates_comment_count()
60 returns trigger language plpgsql
63 IF (TG_OP = 'INSERT') THEN
64 update post_aggregates pa
65 set comments = comments + 1,
66 newest_comment_time = NEW.published
67 where pa.post_id = NEW.post_id;
68 ELSIF (TG_OP = 'DELETE') THEN
69 -- Join to post because that post may not exist anymore
70 update post_aggregates pa
71 set comments = comments - 1
73 where pa.post_id = p.id
74 and pa.post_id = OLD.post_id;
79 create trigger post_aggregates_comment_count
80 after insert or delete on comment
82 execute procedure post_aggregates_comment_count();
85 create function post_aggregates_score()
86 returns trigger language plpgsql
89 IF (TG_OP = 'INSERT') THEN
90 update post_aggregates pa
91 set score = score + NEW.score,
92 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
93 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
94 where pa.post_id = NEW.post_id;
96 ELSIF (TG_OP = 'DELETE') THEN
97 -- Join to post because that post may not exist anymore
98 update post_aggregates pa
99 set score = score - OLD.score,
100 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
101 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
103 where pa.post_id = p.id
104 and pa.post_id = OLD.post_id;
110 create trigger post_aggregates_score
111 after insert or delete on post_like
113 execute procedure post_aggregates_score();