1 -- 2023-06-19-120700_no_double_deletion/up.sql
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 -- 2022-04-04-183652_update_community_aggregates_on_soft_delete/up.sql
22 create or replace function was_restored_or_created(TG_OP text, OLD record, NEW record)
27 IF (TG_OP = 'DELETE') THEN
31 IF (TG_OP = 'INSERT') THEN
35 return TG_OP = 'UPDATE' AND (
36 (OLD.deleted = 't' AND NEW.deleted = 'f') OR
37 (OLD.removed = 't' AND NEW.removed = 'f')
41 -- 2021-08-02-002342_comment_count_fixes/up.sql
42 create or replace function post_aggregates_comment_deleted()
43 returns trigger language plpgsql
46 IF NEW.deleted = TRUE THEN
47 update post_aggregates pa
48 set comments = comments - 1
49 where pa.post_id = NEW.post_id;
51 update post_aggregates pa
52 set comments = comments + 1
53 where pa.post_id = NEW.post_id;
58 create trigger post_aggregates_comment_set_deleted
59 after update of deleted on comment
61 execute procedure post_aggregates_comment_deleted();
63 create or replace function post_aggregates_comment_count()
64 returns trigger language plpgsql
67 IF (TG_OP = 'INSERT') THEN
68 update post_aggregates pa
69 set comments = comments + 1,
70 newest_comment_time = NEW.published
71 where pa.post_id = NEW.post_id;
73 -- A 2 day necro-bump limit
74 update post_aggregates pa
75 set newest_comment_time_necro = NEW.published
77 where pa.post_id = p.id
78 and pa.post_id = NEW.post_id
79 -- Fix issue with being able to necro-bump your own post
80 and NEW.creator_id != p.creator_id
81 and pa.published > ('now'::timestamp - '2 days'::interval);
83 ELSIF (TG_OP = 'DELETE') THEN
84 -- Join to post because that post may not exist anymore
85 update post_aggregates pa
86 set comments = comments - 1
88 where pa.post_id = p.id
89 and pa.post_id = OLD.post_id;
90 ELSIF (TG_OP = 'UPDATE') THEN
91 -- Join to post because that post may not exist anymore
92 update post_aggregates pa
93 set comments = comments - 1
95 where pa.post_id = p.id
96 and pa.post_id = OLD.post_id;
101 -- 2020-12-10-152350_create_post_aggregates/up.sql
102 create or replace trigger post_aggregates_comment_count
103 after insert or delete on comment
105 execute procedure post_aggregates_comment_count();