1 -- Deleting after removing should not decrement the count twice.
2 CREATE OR REPLACE FUNCTION was_removed_or_deleted (TG_OP text, OLD record, NEW record)
7 IF (TG_OP = 'INSERT') THEN
10 IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
13 RETURN TG_OP = 'UPDATE'
14 AND ((OLD.deleted = 'f'
15 AND NEW.deleted = 't')
17 AND NEW.removed = 't'));
21 -- Recalculate proper comment count.
25 comment_count = cnt.count
38 person_aggregates.person_id = cnt.creator_id;
40 -- Recalculate proper comment score.
44 comment_score = cd.score
48 coalesce(0, sum(cl.score)) AS score
49 -- User join because comments could be empty
52 LEFT JOIN comment c ON u.id = c.creator_id
55 LEFT JOIN comment_like cl ON c.id = cl.comment_id
59 ua.person_id = cd.creator_id;
61 -- Recalculate proper post count.
65 post_count = cnt.count
78 person_aggregates.person_id = cnt.creator_id;
80 -- Recalculate proper post score.
88 coalesce(0, sum(pl.score)) AS score
89 -- User join because posts could be empty
92 LEFT JOIN post p ON u.id = p.creator_id
95 LEFT JOIN post_like pl ON p.id = pl.post_id
99 ua.person_id = pd.creator_id;