PostgreSQL: SQL script to get a list of all foreign key references to a table-CodePudding

I have a table, breeds with a primary key of breed_name and would like to get a list of all tables, columns and constraints that reference breeds regardless of column referenced in breeds. If there is another table, cats and that has a constraint as follows:

CREATE TABLE cats 
(
    cat_name  text,
    cat_breed text,

    CONSTRAINT cat_breed_name 
        FOREIGN KEY (cat_breed) REFERENCES breeds(breed_name)
)

I should get back a row like the following:

base_table     base_col    referencing_table   referencing_col  constraint_sql
breeds         breed_name  cats                cat_breed        CONSTRAINT cat_breed_name FOREIGN KEY (cat_breed) REFERENCES breeds(breed_name)

Non-primary key references should also be listed and it should handle compound keys.

CodePudding user response：

You have to JOIN pg_constraint to pg_attribute and un nest the column arrays (could be compound keys) to get the referenced column name in the base table. You have to use pg_class to get the table names. pg_get_constraintdef gives you the actual SQL line that is used to create the constraint.

SELECT (select  r.relname from pg_class r where r.oid = c.confrelid) as base_table,
       a.attname as base_col,
       (select r.relname from pg_class r where r.oid = c.conrelid) as referencing_table,
       UNNEST((select array_agg(attname) from pg_attribute where attrelid = c.conrelid and array[attnum] <@ c.conkey)) as referencing_col,
       pg_get_constraintdef(c.oid) contraint_sql
  FROM pg_constraint c join pg_attribute a on c.confrelid=a.attrelid and a.attnum = ANY(confkey)
 WHERE c.confrelid = (select oid from pg_class where relname = 'breeds')
   AND c.confrelid!=c.conrelid;