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
11 IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
15 return TG_OP = 'UPDATE' AND (
16 (OLD.deleted = 'f' AND NEW.deleted = 't') OR
17 (OLD.removed = 'f' AND NEW.removed = 't')
21 -- Recalculate proper comment count.
22 UPDATE person_aggregates
23 SET comment_count = cnt.count
25 SELECT creator_id, count(*) AS count FROM comment
26 WHERE deleted='f' AND removed='f'
29 WHERE person_aggregates.person_id = cnt.creator_id;
31 -- Recalculate proper comment score.
32 UPDATE person_aggregates ua
33 SET comment_score = cd.score
35 SELECT u.id AS creator_id,
36 coalesce(0, sum(cl.score)) as score
37 -- User join because comments could be empty
39 LEFT JOIN comment c ON u.id = c.creator_id AND c.deleted = 'f' AND c.removed = 'f'
40 LEFT JOIN comment_like cl ON c.id = cl.comment_id
43 WHERE ua.person_id = cd.creator_id;
45 -- Recalculate proper post count.
46 UPDATE person_aggregates
47 SET post_count = cnt.count
49 SELECT creator_id, count(*) AS count FROM post
50 WHERE deleted='f' AND removed='f'
53 WHERE person_aggregates.person_id = cnt.creator_id;
55 -- Recalculate proper post score.
56 UPDATE person_aggregates ua
57 SET post_score = pd.score
59 SELECT u.id AS creator_id,
60 coalesce(0, sum(pl.score)) AS score
61 -- User join because posts could be empty
63 LEFT JOIN post p ON u.id = p.creator_id AND p.deleted = 'f' AND p.removed = 'f'
64 LEFT JOIN post_like pl ON p.id = pl.post_id
67 WHERE ua.person_id = pd.creator_id;