1 CREATE OR REPLACE FUNCTION refresh_community ()
6 IF (TG_OP = 'DELETE') THEN
7 DELETE FROM community_aggregates_fast
9 ELSIF (TG_OP = 'UPDATE') THEN
10 DELETE FROM community_aggregates_fast
12 INSERT INTO community_aggregates_fast
16 community_aggregates_view
19 -- Update user view due to owner changes
21 WHERE id = NEW.creator_id;
29 -- Update post view due to community changes
30 DELETE FROM post_aggregates_fast
31 WHERE community_id = NEW.id;
32 INSERT INTO post_aggregates_fast
38 community_id = NEW.id;
39 -- TODO make sure this shows up in the users page ?
40 ELSIF (TG_OP = 'INSERT') THEN
41 INSERT INTO community_aggregates_fast
45 community_aggregates_view
53 CREATE OR REPLACE FUNCTION refresh_user ()
58 IF (TG_OP = 'DELETE') THEN
61 ELSIF (TG_OP = 'UPDATE') THEN
71 -- Refresh post_fast, cause of user info changes
72 DELETE FROM post_aggregates_fast
73 WHERE creator_id = NEW.id;
74 INSERT INTO post_aggregates_fast
81 DELETE FROM comment_aggregates_fast
82 WHERE creator_id = NEW.id;
83 INSERT INTO comment_aggregates_fast
87 comment_aggregates_view
90 ELSIF (TG_OP = 'INSERT') THEN
103 CREATE OR REPLACE FUNCTION refresh_post ()
108 IF (TG_OP = 'DELETE') THEN
109 DELETE FROM post_aggregates_fast
111 -- Update community number of posts
113 community_aggregates_fast
115 number_of_posts = number_of_posts - 1
117 id = OLD.community_id;
118 ELSIF (TG_OP = 'UPDATE') THEN
119 DELETE FROM post_aggregates_fast
121 INSERT INTO post_aggregates_fast
128 ELSIF (TG_OP = 'INSERT') THEN
129 INSERT INTO post_aggregates_fast
136 -- Update that users number of posts, post score
137 DELETE FROM user_fast
138 WHERE id = NEW.creator_id;
139 INSERT INTO user_fast
146 -- Update community number of posts
148 community_aggregates_fast
150 number_of_posts = number_of_posts + 1
152 id = NEW.community_id;
153 -- Update the hot rank on the post table
154 -- TODO this might not correctly update it, using a 1 week interval
156 post_aggregates_fast AS paf
158 hot_rank = pav.hot_rank
160 post_aggregates_view AS pav
163 AND (pav.published > ('now'::timestamp - '1 week'::interval));
169 CREATE OR REPLACE FUNCTION refresh_comment ()
174 IF (TG_OP = 'DELETE') THEN
175 DELETE FROM comment_aggregates_fast
177 -- Update community number of comments
179 community_aggregates_fast AS caf
181 number_of_comments = number_of_comments - 1
185 caf.id = p.community_id
186 AND p.id = OLD.post_id;
187 ELSIF (TG_OP = 'UPDATE') THEN
188 DELETE FROM comment_aggregates_fast
190 INSERT INTO comment_aggregates_fast
194 comment_aggregates_view
197 ELSIF (TG_OP = 'INSERT') THEN
198 INSERT INTO comment_aggregates_fast
202 comment_aggregates_view
205 -- Update user view due to comment count
209 number_of_comments = number_of_comments + 1
212 -- Update post view due to comment count, new comment activity time, but only on new posts
213 -- TODO this could be done more efficiently
214 DELETE FROM post_aggregates_fast
215 WHERE id = NEW.post_id;
216 INSERT INTO post_aggregates_fast
223 -- Force the hot rank as zero on week-older posts
225 post_aggregates_fast AS paf
230 AND (paf.published < ('now'::timestamp - '1 week'::interval));
231 -- Update community number of comments
233 community_aggregates_fast AS caf
235 number_of_comments = number_of_comments + 1
239 caf.id = p.community_id
240 AND p.id = NEW.post_id;