PostgreSQL soft delete CASCADE

migration.ex

  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