1 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
6 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
10 comment_count = comment_count + 1
12 person_id = NEW.creator_id;
13 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
17 comment_count = comment_count - 1
19 person_id = OLD.creator_id;
20 -- If the comment gets deleted, the score calculation trigger won't fire,
21 -- so you need to re-calculate
25 comment_score = cd.score
29 coalesce(0, sum(cl.score)) AS score
30 -- User join because comments could be empty
33 LEFT JOIN comment c ON u.id = c.creator_id
36 LEFT JOIN comment_like cl ON c.id = cl.comment_id
40 ua.person_id = OLD.creator_id;
46 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
51 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
55 post_count = post_count + 1
57 person_id = NEW.creator_id;
58 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
62 post_count = post_count - 1
64 person_id = OLD.creator_id;
65 -- If the post gets deleted, the score calculation trigger won't fire,
66 -- so you need to re-calculate
74 coalesce(0, sum(pl.score)) AS score
75 -- User join because posts could be empty
78 LEFT JOIN post p ON u.id = p.creator_id
81 LEFT JOIN post_like pl ON p.id = pl.post_id
85 ua.person_id = OLD.creator_id;
91 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
96 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
98 community_aggregates ca
100 comments = comments + 1
106 AND p.id = NEW.post_id
107 AND ca.community_id = p.community_id;
108 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
110 community_aggregates ca
112 comments = comments - 1
118 AND p.id = OLD.post_id
119 AND ca.community_id = p.community_id;