Missing ON UPDATE and ON DELETE triggers for constraints

[oojacoboo]

It appears that, when the foreign keys are added back, the constraints are missing the ON UPDATE and ON DELETE triggers/events.

From the code below that seems to be the case. However, I do see some FK constraints with the triggers. I'm assuming these are copied over somehow though and not actually generated.

    fk_enable = "\n".join(
        [
            cleandoc(
                f"""
            ALTER TABLE {fk['schema']}.{fk['local_table']}
            ADD CONSTRAINT {fk['constraint']} FOREIGN KEY ({fk['local_column']})
            REFERENCES {fk['schema']}.{fk['foreign_table']} ({fk['foreign_column']});
        """
            )
            for fk in get_foreign_keys(host, port, database, schema, table, user, password)
        ]
    )

[oojacoboo]

Here is a pure SQL way to build out the ALTER statements for restoring the constraints. You can build the drops, similarly:

DROP

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname;

ADD

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '|| pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;