postgresql - 在 Postgres 中获取引用表

Question

我有一个外键列表。我想找出这些 FK 指向的表以及指向的实际键。

我有一个像这样的 FK 列表:

columnName0, columnName1, columnName2

外键引用

• columnName0 引用 table0.idTable0
• columnName1 引用 table1.idTable1
• columnName2 引用 table2.idTable2

一些示例表:

表0:

idTable0, PK
name

表 1:

idTable1, PK
age

表2:

idTable2, PK
createdOn

示例结果:

| column      | referenced_column | referenced_table |
|-------------|-------------------|------------------|
| columnName0 | idTable0          | table0           |
| columnName1 | idTable1          | table1           |
| columnName2 | idTable2          | table2           |

我正在尝试像这样翻译我在 MySQL 中所做的事情:

SELECT DISTINCT
    COLUMN_NAME AS column,
    REFERENCED_COLUMN_NAME AS referenced_column,
    REFERENCED_TABLE_NAME AS referenced_table
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    COLUMN_NAME IN (?);

我将不得不使用直接查询(不幸的是，没有存储过程)。

Answer 1

可以查询pg_constraint .对于列名，您应该查找 pg_attribute .一个外键可能基于多个列，所以pg_constraint 的conkey 和confkey 是数组。您必须取消嵌套数组才能获得列名列表。示例:

select 
    conrelid::regclass table_name, 
    a1.attname column_name, 
    confrelid::regclass referenced_table, 
    a2.attname referenced_column,
    conname constraint_name
from (
    select conname, conrelid::regclass, confrelid::regclass, col, fcol
    from pg_constraint c, 
    lateral unnest(conkey) col, 
    lateral unnest(confkey) fcol
    where contype = 'f'   -- foreign keys constraints
    ) s
join pg_attribute a1 on a1.attrelid = conrelid and a1.attnum = col
join pg_attribute a2 on a2.attrelid = confrelid and a2.attnum = fcol;

 table_name | column_name | referenced_table | referenced_column |    constraint_name     
------------+-------------+------------------+-------------------+------------------------
 products   | image_id    | images           | id                | products_image_id_fkey
(1 row)

---

在 Postgres 9.4 或更高版本中，函数 unnest() 可能有多个参数，内部查询可能如下所示:

...
    select conname, conrelid::regclass, confrelid::regclass, col, fcol
    from pg_constraint c, 
    lateral unnest(conkey, confkey) u(col, fcol)
    where contype = 'f'   -- foreign keys constraints
...