1 drop trigger post_aggregates_comment_set_deleted on comment;
2 drop function post_aggregates_comment_deleted;
4 create or replace function post_aggregates_comment_count()
5 returns trigger language plpgsql
8 IF (TG_OP = 'INSERT') THEN
9 update post_aggregates pa
10 set comments = comments + 1,
11 newest_comment_time = NEW.published
12 where pa.post_id = NEW.post_id;
14 -- A 2 day necro-bump limit
15 update post_aggregates pa
16 set newest_comment_time_necro = NEW.published
17 where pa.post_id = NEW.post_id
18 and published > ('now'::timestamp - '2 days'::interval);
19 ELSIF (TG_OP = 'DELETE') THEN
20 -- Join to post because that post may not exist anymore
21 update post_aggregates pa
22 set comments = comments - 1
24 where pa.post_id = p.id
25 and pa.post_id = OLD.post_id;