1 create or replace function refresh_community()
2 returns trigger language plpgsql
5 IF (TG_OP = 'DELETE') THEN
6 delete from community_aggregates_fast where id = OLD.id;
7 ELSIF (TG_OP = 'UPDATE') THEN
8 delete from community_aggregates_fast where id = OLD.id;
9 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id;
11 -- Update user view due to owner changes
12 delete from user_fast where id = NEW.creator_id;
13 insert into user_fast select * from user_view where id = NEW.creator_id;
15 -- Update post view due to community changes
16 delete from post_aggregates_fast where community_id = NEW.id;
17 insert into post_aggregates_fast select * from post_aggregates_view where community_id = NEW.id;
19 -- TODO make sure this shows up in the users page ?
20 ELSIF (TG_OP = 'INSERT') THEN
21 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id;
27 create or replace function refresh_user()
28 returns trigger language plpgsql
31 IF (TG_OP = 'DELETE') THEN
32 delete from user_fast where id = OLD.id;
33 ELSIF (TG_OP = 'UPDATE') THEN
34 delete from user_fast where id = OLD.id;
35 insert into user_fast select * from user_view where id = NEW.id;
37 -- Refresh post_fast, cause of user info changes
38 delete from post_aggregates_fast where creator_id = NEW.id;
39 insert into post_aggregates_fast select * from post_aggregates_view where creator_id = NEW.id;
41 delete from comment_aggregates_fast where creator_id = NEW.id;
42 insert into comment_aggregates_fast select * from comment_aggregates_view where creator_id = NEW.id;
44 ELSIF (TG_OP = 'INSERT') THEN
45 insert into user_fast select * from user_view where id = NEW.id;
51 create or replace function refresh_post()
52 returns trigger language plpgsql
55 IF (TG_OP = 'DELETE') THEN
56 delete from post_aggregates_fast where id = OLD.id;
58 -- Update community number of posts
59 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
60 ELSIF (TG_OP = 'UPDATE') THEN
61 delete from post_aggregates_fast where id = OLD.id;
62 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
63 ELSIF (TG_OP = 'INSERT') THEN
64 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
66 -- Update that users number of posts, post score
67 delete from user_fast where id = NEW.creator_id;
68 insert into user_fast select * from user_view where id = NEW.creator_id;
70 -- Update community number of posts
71 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
73 -- Update the hot rank on the post table
74 -- TODO this might not correctly update it, using a 1 week interval
75 update post_aggregates_fast as paf
76 set hot_rank = pav.hot_rank
77 from post_aggregates_view as pav
78 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
84 create or replace function refresh_comment()
85 returns trigger language plpgsql
88 IF (TG_OP = 'DELETE') THEN
89 delete from comment_aggregates_fast where id = OLD.id;
91 -- Update community number of comments
92 update community_aggregates_fast as caf
93 set number_of_comments = number_of_comments - 1
95 where caf.id = p.community_id and p.id = OLD.post_id;
97 ELSIF (TG_OP = 'UPDATE') THEN
98 delete from comment_aggregates_fast where id = OLD.id;
99 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
100 ELSIF (TG_OP = 'INSERT') THEN
101 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
103 -- Update user view due to comment count
105 set number_of_comments = number_of_comments + 1
106 where id = NEW.creator_id;
108 -- Update post view due to comment count, new comment activity time, but only on new posts
109 -- TODO this could be done more efficiently
110 delete from post_aggregates_fast where id = NEW.post_id;
111 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
113 -- Force the hot rank as zero on week-older posts
114 update post_aggregates_fast as paf
116 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
118 -- Update community number of comments
119 update community_aggregates_fast as caf
120 set number_of_comments = number_of_comments + 1
122 where caf.id = p.community_id and p.id = NEW.post_id;