select-for-version-16-or-lower.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_name = tc.constraint_name
    AND kcu.table_schema = tc.table_schema
JOIN
    information_schema.referential_constraints AS rc
    ON  rc.constraint_name = tc.constraint_name
    AND rc.constraint_schema = tc.constraint_schema
JOIN
    information_schema.key_column_usage AS rkcu
    ON  rkcu.constraint_name = rc.unique_constraint_name
    AND rkcu.table_schema = rc.unique_constraint_schema
WHERE
    tc.constraint_type = 'FOREIGN KEY'
    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;