1 CREATE OR REPLACE FUNCTION refresh_post ()
6 IF (TG_OP = 'DELETE') THEN
7 DELETE FROM post_aggregates_fast
9 -- Update community number of posts
11 community_aggregates_fast
13 number_of_posts = number_of_posts - 1
15 id = OLD.community_id;
16 ELSIF (TG_OP = 'UPDATE') THEN
17 DELETE FROM post_aggregates_fast
19 INSERT INTO post_aggregates_fast
28 ELSIF (TG_OP = 'INSERT') THEN
29 INSERT INTO post_aggregates_fast
36 -- Update that users number of posts, post score
38 WHERE id = NEW.creator_id;
48 -- Update community number of posts
50 community_aggregates_fast
52 number_of_posts = number_of_posts + 1
54 id = NEW.community_id;
55 -- Update the hot rank on the post table
56 -- TODO this might not correctly update it, using a 1 week interval
58 post_aggregates_fast AS paf
60 hot_rank = pav.hot_rank
62 post_aggregates_view AS pav
65 AND (pav.published > ('now'::timestamp - '1 week'::interval));
71 CREATE OR REPLACE FUNCTION refresh_comment ()
76 IF (TG_OP = 'DELETE') THEN
77 DELETE FROM comment_aggregates_fast
79 -- Update community number of comments
81 community_aggregates_fast AS caf
83 number_of_comments = number_of_comments - 1
87 caf.id = p.community_id
88 AND p.id = OLD.post_id;
89 ELSIF (TG_OP = 'UPDATE') THEN
90 DELETE FROM comment_aggregates_fast
92 INSERT INTO comment_aggregates_fast
96 comment_aggregates_view
101 ELSIF (TG_OP = 'INSERT') THEN
102 INSERT INTO comment_aggregates_fast
106 comment_aggregates_view
109 -- Update user view due to comment count
113 number_of_comments = number_of_comments + 1
116 -- Update post view due to comment count, new comment activity time, but only on new posts
117 -- TODO this could be done more efficiently
118 DELETE FROM post_aggregates_fast
119 WHERE id = NEW.post_id;
120 INSERT INTO post_aggregates_fast
129 -- Force the hot rank as zero on week-older posts
131 post_aggregates_fast AS paf
136 AND (paf.published < ('now'::timestamp - '1 week'::interval));
137 -- Update community number of comments
139 community_aggregates_fast AS caf
141 number_of_comments = number_of_comments + 1
145 caf.id = p.community_id
146 AND p.id = NEW.post_id;