1 DROP TRIGGER IF EXISTS community_aggregates_post_count ON post;
3 DROP TRIGGER IF EXISTS community_aggregates_comment_count ON comment;
5 DROP TRIGGER IF EXISTS site_aggregates_comment_insert ON comment;
7 DROP TRIGGER IF EXISTS site_aggregates_comment_delete ON comment;
9 DROP TRIGGER IF EXISTS site_aggregates_post_insert ON post;
11 DROP TRIGGER IF EXISTS site_aggregates_post_delete ON post;
13 DROP TRIGGER IF EXISTS site_aggregates_community_insert ON community;
15 DROP TRIGGER IF EXISTS site_aggregates_community_delete ON community;
17 DROP TRIGGER IF EXISTS person_aggregates_post_count ON post;
19 DROP TRIGGER IF EXISTS person_aggregates_comment_count ON comment;
21 DROP FUNCTION was_removed_or_deleted (TG_OP text, OLD record, NEW record);
23 DROP FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record);
25 -- Community aggregate functions
26 CREATE OR REPLACE FUNCTION community_aggregates_post_count ()
31 IF (TG_OP = 'INSERT') THEN
37 community_id = NEW.community_id;
38 ELSIF (TG_OP = 'DELETE') THEN
44 community_id = OLD.community_id;
45 -- Update the counts if the post got deleted
47 community_aggregates ca
49 posts = coalesce(cd.posts, 0),
50 comments = coalesce(cd.comments, 0)
54 count(DISTINCT p.id) AS posts,
55 count(DISTINCT ct.id) AS comments
58 LEFT JOIN post p ON c.id = p.community_id
59 LEFT JOIN comment ct ON p.id = ct.post_id
63 ca.community_id = OLD.community_id;
69 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
74 IF (TG_OP = 'INSERT') THEN
76 community_aggregates ca
78 comments = comments + 1
84 AND p.id = NEW.post_id
85 AND ca.community_id = p.community_id;
86 ELSIF (TG_OP = 'DELETE') THEN
88 community_aggregates ca
90 comments = comments - 1
96 AND p.id = OLD.post_id
97 AND ca.community_id = p.community_id;
103 -- Community aggregate triggers
104 CREATE TRIGGER community_aggregates_post_count
105 AFTER INSERT OR DELETE ON post
107 EXECUTE PROCEDURE community_aggregates_post_count ();
109 CREATE TRIGGER community_aggregates_comment_count
110 AFTER INSERT OR DELETE ON comment
112 EXECUTE PROCEDURE community_aggregates_comment_count ();
114 -- Site aggregate functions
115 CREATE OR REPLACE FUNCTION site_aggregates_post_insert ()
128 CREATE OR REPLACE FUNCTION site_aggregates_post_delete ()
145 CREATE OR REPLACE FUNCTION site_aggregates_comment_insert ()
153 comments = comments + 1;
158 CREATE OR REPLACE FUNCTION site_aggregates_comment_delete ()
166 comments = comments - 1
175 CREATE OR REPLACE FUNCTION site_aggregates_community_insert ()
183 communities = communities + 1;
188 CREATE OR REPLACE FUNCTION site_aggregates_community_delete ()
196 communities = communities - 1
205 -- Site update triggers
206 CREATE TRIGGER site_aggregates_post_insert
209 WHEN (NEW.local = TRUE)
210 EXECUTE PROCEDURE site_aggregates_post_insert ();
212 CREATE TRIGGER site_aggregates_post_delete
215 WHEN (OLD.local = TRUE)
216 EXECUTE PROCEDURE site_aggregates_post_delete ();
218 CREATE TRIGGER site_aggregates_comment_insert
219 AFTER INSERT ON comment
221 WHEN (NEW.local = TRUE)
222 EXECUTE PROCEDURE site_aggregates_comment_insert ();
224 CREATE TRIGGER site_aggregates_comment_delete
225 AFTER DELETE ON comment
227 WHEN (OLD.local = TRUE)
228 EXECUTE PROCEDURE site_aggregates_comment_delete ();
230 CREATE TRIGGER site_aggregates_community_insert
231 AFTER INSERT ON community
233 WHEN (NEW.local = TRUE)
234 EXECUTE PROCEDURE site_aggregates_community_insert ();
236 CREATE TRIGGER site_aggregates_community_delete
237 AFTER DELETE ON community
239 WHEN (OLD.local = TRUE)
240 EXECUTE PROCEDURE site_aggregates_community_delete ();
242 -- Person aggregate functions
243 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
248 IF (TG_OP = 'INSERT') THEN
252 post_count = post_count + 1
254 person_id = NEW.creator_id;
255 ELSIF (TG_OP = 'DELETE') THEN
259 post_count = post_count - 1
261 person_id = OLD.creator_id;
262 -- If the post gets deleted, the score calculation trigger won't fire,
263 -- so you need to re-calculate
267 post_score = pd.score
271 coalesce(0, sum(pl.score)) AS score
272 -- User join because posts could be empty
275 LEFT JOIN post p ON u.id = p.creator_id
276 LEFT JOIN post_like pl ON p.id = pl.post_id
280 ua.person_id = OLD.creator_id;
286 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
291 IF (TG_OP = 'INSERT') THEN
295 comment_count = comment_count + 1
297 person_id = NEW.creator_id;
298 ELSIF (TG_OP = 'DELETE') THEN
302 comment_count = comment_count - 1
304 person_id = OLD.creator_id;
305 -- If the comment gets deleted, the score calculation trigger won't fire,
306 -- so you need to re-calculate
310 comment_score = cd.score
314 coalesce(0, sum(cl.score)) AS score
315 -- User join because comments could be empty
318 LEFT JOIN comment c ON u.id = c.creator_id
319 LEFT JOIN comment_like cl ON c.id = cl.comment_id
323 ua.person_id = OLD.creator_id;
329 -- Person aggregate triggers
330 CREATE TRIGGER person_aggregates_post_count
331 AFTER INSERT OR DELETE ON post
333 EXECUTE PROCEDURE person_aggregates_post_count ();
335 CREATE TRIGGER person_aggregates_comment_count
336 AFTER INSERT OR DELETE ON comment
338 EXECUTE PROCEDURE person_aggregates_comment_count ();