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 published timestamp not null default now(),
10 newest_comment_time timestamp not null default now(),
14 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, published, newest_comment_time)
17 coalesce(ct.comments, 0::bigint) as comments,
18 coalesce(pl.score, 0::bigint) as score,
19 coalesce(pl.upvotes, 0::bigint) as upvotes,
20 coalesce(pl.downvotes, 0::bigint) as downvotes,
22 greatest(ct.recent_comment_time, p.published) as newest_activity_time
25 select comment.post_id,
27 max(comment.published) as recent_comment_time
29 group by comment.post_id
30 ) ct on ct.post_id = p.id
32 select post_like.post_id,
33 sum(post_like.score) as score,
34 sum(post_like.score) filter (where post_like.score = 1) as upvotes,
35 -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
37 group by post_like.post_id
38 ) pl on pl.post_id = p.id;
40 -- Add community aggregate triggers
43 create function post_aggregates_post()
44 returns trigger language plpgsql
47 IF (TG_OP = 'INSERT') THEN
48 insert into post_aggregates (post_id) values (NEW.id);
49 ELSIF (TG_OP = 'DELETE') THEN
50 delete from post_aggregates where post_id = OLD.id;
55 create trigger post_aggregates_post
56 after insert or delete on post
58 execute procedure post_aggregates_post();
61 create function post_aggregates_comment_count()
62 returns trigger language plpgsql
65 IF (TG_OP = 'INSERT') THEN
66 update post_aggregates pa
67 set comments = comments + 1,
68 newest_comment_time = NEW.published
69 where pa.post_id = NEW.post_id
70 -- A 2 day necro-bump limit
71 and published > ('now'::timestamp - '2 days'::interval);
72 ELSIF (TG_OP = 'DELETE') THEN
73 -- Join to post because that post may not exist anymore
74 update post_aggregates pa
75 set comments = comments - 1
77 where pa.post_id = p.id
78 and pa.post_id = OLD.post_id;
83 create trigger post_aggregates_comment_count
84 after insert or delete on comment
86 execute procedure post_aggregates_comment_count();
89 create function post_aggregates_score()
90 returns trigger language plpgsql
93 IF (TG_OP = 'INSERT') THEN
94 update post_aggregates pa
95 set score = score + NEW.score,
96 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
97 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
98 where pa.post_id = NEW.post_id;
100 ELSIF (TG_OP = 'DELETE') THEN
101 -- Join to post because that post may not exist anymore
102 update post_aggregates pa
103 set score = score - OLD.score,
104 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
105 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
107 where pa.post_id = p.id
108 and pa.post_id = OLD.post_id;
114 create trigger post_aggregates_score
115 after insert or delete on post_like
117 execute procedure post_aggregates_score();