select-for-version-17.sql

SELECT
    tc.table_name,
    tc.constraint_name,
    tc.constraint_type,
    'alter table if exists '
    ||quote_ident(tc.table_name)
    ||' add constraint '
    ||quote_ident(tc.constraint_name)
    ||' foreign key ('
    ||string_agg(quote_ident(kcu.column_name), ', ' ORDER BY kcu.ordinal_position)
    ||')'
    ||' references '
    ||quote_ident(rkcu.table_name)
    ||' ('
    ||string_agg(quote_ident(rkcu.column_name), ', ' ORDER BY rkcu.ordinal_position)
    ||')'
    ||' on delete '
    ||CASE
        WHEN rc.delete_rule = 'CASCADE' THEN 'cascade'
        WHEN rc.delete_rule = 'SET NULL' THEN 'set null'
        WHEN rc.delete_rule = 'SET DEFAULT' THEN 'set default'
        ELSE 'no action'
        END
    ||' on update '
    ||case
        WHEN rc.update_rule = 'CASCADE' THEN 'cascade'
        WHEN rc.update_rule = 'SET NULL' THEN 'set null'
        WHEN rc.update_rule = 'SET DEFAULT' THEN 'set default'
        ELSE 'no action'
        END
    ||';'
    AS create_statement,
    'alter table if exists '
    ||quote_ident(tc.table_name)
    ||' drop constraint if exists '
    ||quote_ident(tc.constraint_name)
    ||' cascade;'
    AS drop_statement
FROM
    information_schema.table_constraints AS tc
JOIN
    information_schema.key_column_usage AS kcu
    ON kcu.constraint_catalog = 'sigex'
    AND kcu.constraint_schema = 'second'
    AND kcu.table_catalog = 'sigex'
    AND kcu.table_schema = 'second'
    AND kcu.constraint_name = tc.constraint_name
    AND kcu.table_name = tc.table_name
JOIN
    information_schema.referential_constraints AS rc
    ON rc.constraint_catalog = 'sigex'
    AND rc.constraint_schema = 'second'
    AND rc.constraint_name = tc.constraint_name
JOIN
    information_schema.key_column_usage AS rkcu
    ON rkcu.constraint_catalog = 'sigex'
    AND rkcu.constraint_schema = 'second'
    AND rkcu.table_catalog = 'sigex'
    AND rkcu.table_schema = 'second'
    AND rkcu.constraint_name = rc.unique_constraint_name
WHERE
    tc.constraint_type = 'FOREIGN KEY'
    AND tc.constraint_catalog = 'sigex'
    AND tc.constraint_schema = 'second'
    AND tc.table_catalog = 'sigex'
    AND tc.table_schema = 'second'
    AND tc.table_name not ilike 'zyx\_%' escape '\'
GROUP BY
    tc.table_name,
    tc.constraint_name,
    tc.constraint_type,
    rkcu.table_name,
    rc.delete_rule,
    rc.update_rule
ORDER BY
    tc.table_name,
    tc.constraint_name;