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
31 max(comment.published) AS recent_comment_time
35 comment.post_id) ct ON ct.post_id = p.id
39 sum(post_like.score) AS score,
40 sum(post_like.score) FILTER (WHERE post_like.score = 1) AS upvotes,
41 - sum(post_like.score) FILTER (WHERE post_like.score = '-1'::integer) AS downvotes
45 post_like.post_id) pl ON pl.post_id = p.id;
47 -- Add community aggregate triggers
49 CREATE FUNCTION post_aggregates_post ()
54 IF (TG_OP = 'INSERT') THEN
55 INSERT INTO post_aggregates (post_id)
57 ELSIF (TG_OP = 'DELETE') THEN
58 DELETE FROM post_aggregates
59 WHERE post_id = OLD.id;
65 CREATE TRIGGER post_aggregates_post
66 AFTER INSERT OR DELETE ON post
68 EXECUTE PROCEDURE post_aggregates_post ();
71 CREATE FUNCTION post_aggregates_comment_count ()
76 IF (TG_OP = 'INSERT') THEN
80 comments = comments + 1
82 pa.post_id = NEW.post_id;
83 -- A 2 day necro-bump limit
87 newest_comment_time = NEW.published
89 pa.post_id = NEW.post_id
90 AND published > ('now'::timestamp - '2 days'::interval);
91 ELSIF (TG_OP = 'DELETE') THEN
92 -- Join to post because that post may not exist anymore
96 comments = comments - 1
101 AND pa.post_id = OLD.post_id;
107 CREATE TRIGGER post_aggregates_comment_count
108 AFTER INSERT OR DELETE ON comment
110 EXECUTE PROCEDURE post_aggregates_comment_count ();
113 CREATE FUNCTION post_aggregates_score ()
118 IF (TG_OP = 'INSERT') THEN
122 score = score + NEW.score,
123 upvotes = CASE WHEN NEW.score = 1 THEN
128 downvotes = CASE WHEN NEW.score = - 1 THEN
134 pa.post_id = NEW.post_id;
135 ELSIF (TG_OP = 'DELETE') THEN
136 -- Join to post because that post may not exist anymore
140 score = score - OLD.score,
141 upvotes = CASE WHEN OLD.score = 1 THEN
146 downvotes = CASE WHEN OLD.score = - 1 THEN
155 AND pa.post_id = OLD.post_id;
161 CREATE TRIGGER post_aggregates_score
162 AFTER INSERT OR DELETE ON post_like
164 EXECUTE PROCEDURE post_aggregates_score ();
167 CREATE FUNCTION post_aggregates_stickied ()
175 stickied = NEW.stickied
182 CREATE TRIGGER post_aggregates_stickied
185 WHEN (OLD.stickied IS DISTINCT FROM NEW.stickied)
186 EXECUTE PROCEDURE post_aggregates_stickied ();