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 stickied boolean not null default false,
10 published timestamp not null default now(),
11 newest_comment_time timestamp not null default now(),
15 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, stickied, published, newest_comment_time)
18 coalesce(ct.comments, 0::bigint) as comments,
19 coalesce(pl.score, 0::bigint) as score,
20 coalesce(pl.upvotes, 0::bigint) as upvotes,
21 coalesce(pl.downvotes, 0::bigint) as downvotes,
24 greatest(ct.recent_comment_time, p.published) as newest_activity_time
27 select comment.post_id,
29 max(comment.published) as recent_comment_time
31 group by comment.post_id
32 ) ct on ct.post_id = p.id
34 select post_like.post_id,
35 sum(post_like.score) as score,
36 sum(post_like.score) filter (where post_like.score = 1) as upvotes,
37 -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
39 group by post_like.post_id
40 ) pl on pl.post_id = p.id;
42 -- Add community aggregate triggers
45 create function post_aggregates_post()
46 returns trigger language plpgsql
49 IF (TG_OP = 'INSERT') THEN
50 insert into post_aggregates (post_id) values (NEW.id);
51 ELSIF (TG_OP = 'DELETE') THEN
52 delete from post_aggregates where post_id = OLD.id;
57 create trigger post_aggregates_post
58 after insert or delete on post
60 execute procedure post_aggregates_post();
63 create function post_aggregates_comment_count()
64 returns trigger language plpgsql
67 IF (TG_OP = 'INSERT') THEN
68 update post_aggregates pa
69 set comments = comments + 1,
70 newest_comment_time = NEW.published
71 where pa.post_id = NEW.post_id
72 -- A 2 day necro-bump limit
73 and published > ('now'::timestamp - '2 days'::interval);
74 ELSIF (TG_OP = 'DELETE') THEN
75 -- Join to post because that post may not exist anymore
76 update post_aggregates pa
77 set comments = comments - 1
79 where pa.post_id = p.id
80 and pa.post_id = OLD.post_id;
85 create trigger post_aggregates_comment_count
86 after insert or delete on comment
88 execute procedure post_aggregates_comment_count();
91 create function post_aggregates_score()
92 returns trigger language plpgsql
95 IF (TG_OP = 'INSERT') THEN
96 update post_aggregates pa
97 set score = score + NEW.score,
98 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
99 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
100 where pa.post_id = NEW.post_id;
102 ELSIF (TG_OP = 'DELETE') THEN
103 -- Join to post because that post may not exist anymore
104 update post_aggregates pa
105 set score = score - OLD.score,
106 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
107 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
109 where pa.post_id = p.id
110 and pa.post_id = OLD.post_id;
116 create trigger post_aggregates_score
117 after insert or delete on post_like
119 execute procedure post_aggregates_score();
122 create function post_aggregates_stickied()
123 returns trigger language plpgsql
126 update post_aggregates pa
127 set stickied = NEW.stickied
128 where pa.post_id = NEW.id;
133 create trigger post_aggregates_stickied
136 when (OLD.stickied is distinct from NEW.stickied)
137 execute procedure post_aggregates_stickied();