1 -- Creating a new trigger for when comment.deleted is updated
3 create or replace function post_aggregates_comment_deleted()
4 returns trigger language plpgsql
7 IF NEW.deleted = TRUE THEN
8 update post_aggregates pa
9 set comments = comments - 1
10 where pa.post_id = NEW.post_id;
12 update post_aggregates pa
13 set comments = comments + 1
14 where pa.post_id = NEW.post_id;
19 create trigger post_aggregates_comment_set_deleted
20 after update of deleted on comment
22 execute procedure post_aggregates_comment_deleted();
24 -- Fix issue with being able to necro-bump your own post
25 create or replace function post_aggregates_comment_count()
26 returns trigger language plpgsql
29 IF (TG_OP = 'INSERT') THEN
30 update post_aggregates pa
31 set comments = comments + 1,
32 newest_comment_time = NEW.published
33 where pa.post_id = NEW.post_id;
35 -- A 2 day necro-bump limit
36 update post_aggregates pa
37 set newest_comment_time_necro = NEW.published
39 where pa.post_id = p.id
40 and pa.post_id = NEW.post_id
41 -- Fix issue with being able to necro-bump your own post
42 and NEW.creator_id != p.creator_id
43 and pa.published > ('now'::timestamp - '2 days'::interval);
45 ELSIF (TG_OP = 'DELETE') THEN
46 -- Join to post because that post may not exist anymore
47 update post_aggregates pa
48 set comments = comments - 1
50 where pa.post_id = p.id
51 and pa.post_id = OLD.post_id;
52 ELSIF (TG_OP = 'UPDATE') THEN
53 -- Join to post because that post may not exist anymore
54 update post_aggregates pa
55 set comments = comments - 1
57 where pa.post_id = p.id
58 and pa.post_id = OLD.post_id;