PostgreSQL：SQL脚本以获取所有外键参考的列表

Question

我有一个表格，品种，带有breed_name的主要键，并希望获取参考breeds的所有表，列和约束列表不论breeds中引用的列如何。如果还有另一个表，则cats，其约束如下：

CREATE TABLE cats 
(
    cat_name  text,
    cat_breed text,

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

我应该返回一行，如下：

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)

非主要密钥参考文献也应列出，并且应该处理复合键。

Answer 1

您必须将pg_constraint加入pg_attribute并嵌套列库阵列（可以是复合键），以在基础表中获取引用的列名。
您必须使用pg_class获取表名称。
pg_get_constraintdef为您提供了用于创建约束的实际SQL行。

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;