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 update post_aggregates pa
70 set comments = comments - 1
71 where pa.post_id = OLD.post_id;
76 create trigger post_aggregates_comment_count
77 after insert or delete on comment
79 execute procedure post_aggregates_comment_count();
82 create function post_aggregates_score()
83 returns trigger language plpgsql
86 IF (TG_OP = 'INSERT') THEN
87 update post_aggregates pa
88 set score = score + NEW.score,
89 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
90 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
91 where pa.post_id = NEW.post_id;
93 ELSIF (TG_OP = 'DELETE') THEN
94 update post_aggregates pa
95 set score = score - OLD.score,
96 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
97 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
98 where pa.post_id = OLD.post_id;
104 create trigger post_aggregates_score
105 after insert or delete on post_like
107 execute procedure post_aggregates_score();