Skip to content

Instantly share code, notes, and snippets.

@ibarchenkov
Last active August 22, 2022 09:09
Show Gist options
  • Save ibarchenkov/21bc0b293e1896386c043508a31b1193 to your computer and use it in GitHub Desktop.
Save ibarchenkov/21bc0b293e1896386c043508a31b1193 to your computer and use it in GitHub Desktop.
PostgreSQL soft delete CASCADE
def create_soft_delete_cascade_function() do
execute(
"""
CREATE OR REPLACE FUNCTION soft_delete_cascade() RETURNS trigger AS $$
DECLARE
ref RECORD;
BEGIN
IF EXISTS (SELECT true FROM new_table WHERE deleted_at IS NOT NULL) THEN
FOR ref IN
SELECT
DISTINCT ON (pg_constraint.conrelid) pg_constraint.conrelid::regclass AS child_table,
attr2.attname AS child_column,
attr3.attname AS parent_column
FROM pg_catalog.pg_constraint pg_constraint
JOIN pg_attribute attr1 ON attr1.attrelid = pg_constraint.conrelid AND attr1.attname = 'deleted_at' AND NOT attr1.attisdropped
JOIN pg_attribute attr2 ON attr2.attrelid = pg_constraint.conrelid AND attr2.attnum = pg_constraint.conkey[1]
JOIN pg_attribute attr3 ON attr3.attrelid = pg_constraint.confrelid AND attr3.attnum = pg_constraint.confkey[1]
WHERE
pg_constraint.confdeltype = 'c'
AND pg_constraint.confrelid = TG_RELID
AND array_length(pg_constraint.conkey, 1) = 1
AND array_length(pg_constraint.confkey, 1) = 1
LOOP
EXECUTE format(
'UPDATE %I child
SET deleted_at = parent.deleted_at
FROM new_table parent
WHERE parent.deleted_at IS NOT NULL AND parent.%I = child.%I', ref.child_table, ref.parent_column, ref.child_column);
END LOOP;
END IF;
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
""",
"DROP FUNCTION IF EXISTS soft_delete_cascade();"
)
end
def create_soft_delete_cascade_trigger(table) do
execute(
"""
CREATE TRIGGER soft_delete_cascade
AFTER UPDATE ON #{table}
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION soft_delete_cascade();
""",
"DROP TRIGGER IF EXISTS soft_delete_cascade ON #{table};"
)
end
def create_soft_delete_rule(table, pk \\ "id") do
execute(
"""
CREATE OR REPLACE RULE soft_delete AS ON DELETE
TO #{table}
DO INSTEAD UPDATE #{table} SET deleted_at = now() WHERE #{pk} = OLD.#{pk};
""",
"DROP RULE IF EXISTS soft_delete ON #{table};"
)
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment