1 drop trigger if exists community_aggregates_post_count on post;
2 drop trigger if exists community_aggregates_comment_count on comment;
3 drop trigger if exists site_aggregates_comment_insert on comment;
4 drop trigger if exists site_aggregates_comment_delete on comment;
5 drop trigger if exists site_aggregates_post_insert on post;
6 drop trigger if exists site_aggregates_post_delete on post;
7 drop trigger if exists site_aggregates_community_insert on community;
8 drop trigger if exists site_aggregates_community_delete on community;
9 drop trigger if exists person_aggregates_post_count on post;
10 drop trigger if exists person_aggregates_comment_count on comment;
12 drop function was_removed_or_deleted(TG_OP text, OLD record, NEW record);
13 drop function was_restored_or_created(TG_OP text, OLD record, NEW record);
15 -- Community aggregate functions
17 create or replace function community_aggregates_post_count()
18 returns trigger language plpgsql
21 IF (TG_OP = 'INSERT') THEN
22 update community_aggregates
23 set posts = posts + 1 where community_id = NEW.community_id;
24 ELSIF (TG_OP = 'DELETE') THEN
25 update community_aggregates
26 set posts = posts - 1 where community_id = OLD.community_id;
28 -- Update the counts if the post got deleted
29 update community_aggregates ca
30 set posts = coalesce(cd.posts, 0),
31 comments = coalesce(cd.comments, 0)
35 count(distinct p.id) as posts,
36 count(distinct ct.id) as comments
38 left join post p on c.id = p.community_id
39 left join comment ct on p.id = ct.post_id
42 where ca.community_id = OLD.community_id;
47 create or replace function community_aggregates_comment_count()
48 returns trigger language plpgsql
51 IF (TG_OP = 'INSERT') THEN
52 update community_aggregates ca
53 set comments = comments + 1 from comment c, post p
54 where p.id = c.post_id
55 and p.id = NEW.post_id
56 and ca.community_id = p.community_id;
57 ELSIF (TG_OP = 'DELETE') THEN
58 update community_aggregates ca
59 set comments = comments - 1 from comment c, post p
60 where p.id = c.post_id
61 and p.id = OLD.post_id
62 and ca.community_id = p.community_id;
68 -- Community aggregate triggers
70 create trigger community_aggregates_post_count
71 after insert or delete on post
73 execute procedure community_aggregates_post_count();
75 create trigger community_aggregates_comment_count
76 after insert or delete on comment
78 execute procedure community_aggregates_comment_count();
80 -- Site aggregate functions
82 create or replace function site_aggregates_post_insert()
83 returns trigger language plpgsql
86 update site_aggregates
87 set posts = posts + 1;
91 create or replace function site_aggregates_post_delete()
92 returns trigger language plpgsql
95 update site_aggregates sa
98 where sa.site_id = s.id;
102 create or replace function site_aggregates_comment_insert()
103 returns trigger language plpgsql
106 update site_aggregates
107 set comments = comments + 1;
111 create or replace function site_aggregates_comment_delete()
112 returns trigger language plpgsql
115 update site_aggregates sa
116 set comments = comments - 1
118 where sa.site_id = s.id;
122 create or replace function site_aggregates_community_insert()
123 returns trigger language plpgsql
126 update site_aggregates
127 set communities = communities + 1;
131 create or replace function site_aggregates_community_delete()
132 returns trigger language plpgsql
135 update site_aggregates sa
136 set communities = communities - 1
138 where sa.site_id = s.id;
143 -- Site update triggers
145 create trigger site_aggregates_post_insert
148 when (NEW.local = true)
149 execute procedure site_aggregates_post_insert();
151 create trigger site_aggregates_post_delete
154 when (OLD.local = true)
155 execute procedure site_aggregates_post_delete();
157 create trigger site_aggregates_comment_insert
158 after insert on comment
160 when (NEW.local = true)
161 execute procedure site_aggregates_comment_insert();
163 create trigger site_aggregates_comment_delete
164 after delete on comment
166 when (OLD.local = true)
167 execute procedure site_aggregates_comment_delete();
169 create trigger site_aggregates_community_insert
170 after insert on community
172 when (NEW.local = true)
173 execute procedure site_aggregates_community_insert();
175 create trigger site_aggregates_community_delete
176 after delete on community
178 when (OLD.local = true)
179 execute procedure site_aggregates_community_delete();
181 -- Person aggregate functions
183 create or replace function person_aggregates_post_count()
184 returns trigger language plpgsql
187 IF (TG_OP = 'INSERT') THEN
188 update person_aggregates
189 set post_count = post_count + 1 where person_id = NEW.creator_id;
191 ELSIF (TG_OP = 'DELETE') THEN
192 update person_aggregates
193 set post_count = post_count - 1 where person_id = OLD.creator_id;
195 -- If the post gets deleted, the score calculation trigger won't fire,
196 -- so you need to re-calculate
197 update person_aggregates ua
198 set post_score = pd.score
201 coalesce(0, sum(pl.score)) as score
202 -- User join because posts could be empty
204 left join post p on u.id = p.creator_id
205 left join post_like pl on p.id = pl.post_id
208 where ua.person_id = OLD.creator_id;
214 create or replace function person_aggregates_comment_count()
215 returns trigger language plpgsql
218 IF (TG_OP = 'INSERT') THEN
219 update person_aggregates
220 set comment_count = comment_count + 1 where person_id = NEW.creator_id;
221 ELSIF (TG_OP = 'DELETE') THEN
222 update person_aggregates
223 set comment_count = comment_count - 1 where person_id = OLD.creator_id;
225 -- If the comment gets deleted, the score calculation trigger won't fire,
226 -- so you need to re-calculate
227 update person_aggregates ua
228 set comment_score = cd.score
231 coalesce(0, sum(cl.score)) as score
232 -- User join because comments could be empty
234 left join comment c on u.id = c.creator_id
235 left join comment_like cl on c.id = cl.comment_id
238 where ua.person_id = OLD.creator_id;
244 -- Person aggregate triggers
246 create trigger person_aggregates_post_count
247 after insert or delete on post
249 execute procedure person_aggregates_post_count();
251 create trigger person_aggregates_comment_count
252 after insert or delete on comment
254 execute procedure person_aggregates_comment_count();