1 create or replace function person_aggregates_comment_count()
2 returns trigger language plpgsql
5 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
6 update person_aggregates
7 set comment_count = comment_count + 1 where person_id = NEW.creator_id;
8 ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
9 update person_aggregates
10 set comment_count = comment_count - 1 where person_id = OLD.creator_id;
12 -- If the comment gets deleted, the score calculation trigger won't fire,
13 -- so you need to re-calculate
14 update person_aggregates ua
15 set comment_score = cd.score
18 coalesce(0, sum(cl.score)) as score
19 -- User join because comments could be empty
21 left join comment c on u.id = c.creator_id and c.deleted = 'f' and c.removed = 'f'
22 left join comment_like cl on c.id = cl.comment_id
25 where ua.person_id = OLD.creator_id;
30 create or replace function person_aggregates_post_count()
31 returns trigger language plpgsql
34 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
35 update person_aggregates
36 set post_count = post_count + 1 where person_id = NEW.creator_id;
38 ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
39 update person_aggregates
40 set post_count = post_count - 1 where person_id = OLD.creator_id;
42 -- If the post gets deleted, the score calculation trigger won't fire,
43 -- so you need to re-calculate
44 update person_aggregates ua
45 set post_score = pd.score
48 coalesce(0, sum(pl.score)) as score
49 -- User join because posts could be empty
51 left join post p on u.id = p.creator_id and p.deleted = 'f' and p.removed = 'f'
52 left join post_like pl on p.id = pl.post_id
55 where ua.person_id = OLD.creator_id;
61 create or replace function community_aggregates_comment_count()
62 returns trigger language plpgsql
65 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
66 update community_aggregates ca
67 set comments = comments + 1 from comment c, post p
68 where p.id = c.post_id
69 and p.id = NEW.post_id
70 and ca.community_id = p.community_id;
71 ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
72 update community_aggregates ca
73 set comments = comments - 1 from comment c, post p
74 where p.id = c.post_id
75 and p.id = OLD.post_id
76 and ca.community_id = p.community_id;