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
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 -- 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
30 IF (TG_OP = 'INSERT') THEN
33 RETURN TG_OP = 'UPDATE'
34 AND ((OLD.deleted = 't'
35 AND NEW.deleted = 'f')
37 AND NEW.removed = 'f'));
41 -- 2021-08-02-002342_comment_count_fixes/up.sql
42 CREATE OR REPLACE FUNCTION post_aggregates_comment_deleted ()
47 IF NEW.deleted = TRUE THEN
51 comments = comments - 1
53 pa.post_id = NEW.post_id;
58 comments = comments + 1
60 pa.post_id = NEW.post_id;
66 CREATE TRIGGER post_aggregates_comment_set_deleted
67 AFTER UPDATE OF deleted ON comment
69 EXECUTE PROCEDURE post_aggregates_comment_deleted ();
71 CREATE OR REPLACE FUNCTION post_aggregates_comment_count ()
76 IF (TG_OP = 'INSERT') THEN
80 comments = comments + 1,
81 newest_comment_time = NEW.published
83 pa.post_id = NEW.post_id;
84 -- A 2 day necro-bump limit
88 newest_comment_time_necro = NEW.published
93 AND pa.post_id = NEW.post_id
94 -- Fix issue with being able to necro-bump your own post
95 AND NEW.creator_id != p.creator_id
96 AND pa.published > ('now'::timestamp - '2 days'::interval);
97 ELSIF (TG_OP = 'DELETE') THEN
98 -- Join to post because that post may not exist anymore
102 comments = comments - 1
107 AND pa.post_id = OLD.post_id;
108 ELSIF (TG_OP = 'UPDATE') THEN
109 -- Join to post because that post may not exist anymore
113 comments = comments - 1
118 AND pa.post_id = OLD.post_id;
124 -- 2020-12-10-152350_create_post_aggregates/up.sql
125 CREATE OR REPLACE TRIGGER post_aggregates_comment_count
126 AFTER INSERT OR DELETE ON comment
128 EXECUTE PROCEDURE post_aggregates_comment_count ();