1 create or replace function refresh_post()
2 returns trigger language plpgsql
5 IF (TG_OP = 'DELETE') THEN
6 delete from post_aggregates_fast where id = OLD.id;
8 -- Update community number of posts
9 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
10 ELSIF (TG_OP = 'UPDATE') THEN
11 delete from post_aggregates_fast where id = OLD.id;
12 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id on conflict (id) do nothing;
14 ELSIF (TG_OP = 'INSERT') THEN
15 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
17 -- Update that users number of posts, post score
18 delete from user_fast where id = NEW.creator_id;
19 insert into user_fast select * from user_view where id = NEW.creator_id on conflict (id) do nothing;
21 -- Update community number of posts
22 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
24 -- Update the hot rank on the post table
25 -- TODO this might not correctly update it, using a 1 week interval
26 update post_aggregates_fast as paf
27 set hot_rank = pav.hot_rank
28 from post_aggregates_view as pav
29 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
36 create or replace function refresh_comment()
37 returns trigger language plpgsql
40 IF (TG_OP = 'DELETE') THEN
41 delete from comment_aggregates_fast where id = OLD.id;
43 -- Update community number of comments
44 update community_aggregates_fast as caf
45 set number_of_comments = number_of_comments - 1
47 where caf.id = p.community_id and p.id = OLD.post_id;
49 ELSIF (TG_OP = 'UPDATE') THEN
50 delete from comment_aggregates_fast where id = OLD.id;
51 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id on conflict (id) do nothing;
52 ELSIF (TG_OP = 'INSERT') THEN
53 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
55 -- Update user view due to comment count
57 set number_of_comments = number_of_comments + 1
58 where id = NEW.creator_id;
60 -- Update post view due to comment count, new comment activity time, but only on new posts
61 -- TODO this could be done more efficiently
62 delete from post_aggregates_fast where id = NEW.post_id;
63 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id on conflict (id) do nothing;
65 -- Force the hot rank as zero on week-older posts
66 update post_aggregates_fast as paf
68 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
70 -- Update community number of comments
71 update community_aggregates_fast as caf
72 set number_of_comments = number_of_comments + 1
74 where caf.id = p.community_id and p.id = NEW.post_id;