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 where pa.post_id = NEW.post_id;
72 -- A 2 day necro-bump limit
73 update post_aggregates pa
74 set newest_comment_time = NEW.published
75 where pa.post_id = NEW.post_id
76 and published > ('now'::timestamp - '2 days'::interval);
77 ELSIF (TG_OP = 'DELETE') THEN
78 -- Join to post because that post may not exist anymore
79 update post_aggregates pa
80 set comments = comments - 1
82 where pa.post_id = p.id
83 and pa.post_id = OLD.post_id;
88 create trigger post_aggregates_comment_count
89 after insert or delete on comment
91 execute procedure post_aggregates_comment_count();
94 create function post_aggregates_score()
95 returns trigger language plpgsql
98 IF (TG_OP = 'INSERT') THEN
99 update post_aggregates pa
100 set score = score + NEW.score,
101 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
102 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
103 where pa.post_id = NEW.post_id;
105 ELSIF (TG_OP = 'DELETE') THEN
106 -- Join to post because that post may not exist anymore
107 update post_aggregates pa
108 set score = score - OLD.score,
109 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
110 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
112 where pa.post_id = p.id
113 and pa.post_id = OLD.post_id;
119 create trigger post_aggregates_score
120 after insert or delete on post_like
122 execute procedure post_aggregates_score();
125 create function post_aggregates_stickied()
126 returns trigger language plpgsql
129 update post_aggregates pa
130 set stickied = NEW.stickied
131 where pa.post_id = NEW.id;
136 create trigger post_aggregates_stickied
139 when (OLD.stickied is distinct from NEW.stickied)
140 execute procedure post_aggregates_stickied();