1 -- Forgot to add hot rank active to these two triggers
2 CREATE OR REPLACE FUNCTION refresh_post ()
7 IF (TG_OP = 'DELETE') THEN
8 DELETE FROM post_aggregates_fast
10 -- Update community number of posts
12 community_aggregates_fast
14 number_of_posts = number_of_posts - 1
16 id = OLD.community_id;
17 ELSIF (TG_OP = 'UPDATE') THEN
18 DELETE FROM post_aggregates_fast
20 INSERT INTO post_aggregates_fast
29 ELSIF (TG_OP = 'INSERT') THEN
30 INSERT INTO post_aggregates_fast
37 -- Update that users number of posts, post score
39 WHERE id = NEW.creator_id;
49 -- Update community number of posts
51 community_aggregates_fast
53 number_of_posts = number_of_posts + 1
55 id = NEW.community_id;
56 -- Update the hot rank on the post table
57 -- TODO this might not correctly update it, using a 1 week interval
59 post_aggregates_fast AS paf
61 hot_rank = pav.hot_rank,
62 hot_rank_active = pav.hot_rank_active
64 post_aggregates_view AS pav
67 AND (pav.published > ('now'::timestamp - '1 week'::interval));
73 CREATE OR REPLACE FUNCTION refresh_comment ()
78 IF (TG_OP = 'DELETE') THEN
79 DELETE FROM comment_aggregates_fast
81 -- Update community number of comments
83 community_aggregates_fast AS caf
85 number_of_comments = number_of_comments - 1
89 caf.id = p.community_id
90 AND p.id = OLD.post_id;
91 ELSIF (TG_OP = 'UPDATE') THEN
92 DELETE FROM comment_aggregates_fast
94 INSERT INTO comment_aggregates_fast
98 comment_aggregates_view
103 ELSIF (TG_OP = 'INSERT') THEN
104 INSERT INTO comment_aggregates_fast
108 comment_aggregates_view
111 -- Update user view due to comment count
115 number_of_comments = number_of_comments + 1
118 -- Update post view due to comment count, new comment activity time, but only on new posts
119 -- TODO this could be done more efficiently
120 DELETE FROM post_aggregates_fast
121 WHERE id = NEW.post_id;
122 INSERT INTO post_aggregates_fast
131 -- Update the comment hot_ranks as of last week
133 comment_aggregates_fast AS caf
135 hot_rank = cav.hot_rank,
136 hot_rank_active = cav.hot_rank_active
138 comment_aggregates_view AS cav
141 AND (cav.published > ('now'::timestamp - '1 week'::interval));
142 -- Update the post ranks
144 post_aggregates_fast AS paf
146 hot_rank = pav.hot_rank,
147 hot_rank_active = pav.hot_rank_active
149 post_aggregates_view AS pav
152 AND (pav.published > ('now'::timestamp - '1 week'::interval));
153 -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
155 post_aggregates_fast AS paf
160 AND (paf.published < ('now'::timestamp - '2 days'::interval));
161 -- Update community number of comments
163 community_aggregates_fast AS caf
165 number_of_comments = number_of_comments + 1
169 caf.id = p.community_id
170 AND p.id = NEW.post_id;