列出所有外键PostgreSQL [英] List all foreign keys PostgreSQL

问题描述

我需要一个查询，返回以下内容：

I need a query that returns:

表名，字段名，字段类型，约束名

"table_name", "field_name", "field_type", "contraint_name"

直到现在我拥有：

select conrelid::regclass AS table_name,
       regexp_replace(pg_get_constraintdef(c.oid), '.*\((.*)\)', '\1') as fields,
       conname as contraint_name
from   pg_constraint c
join   pg_namespace n ON n.oid = c.connamespace
join   pg_attribute at on 
--join   pg_type t ON t.typnamespace = n.oid
where  contype ='f' 

推荐答案

外键可能基于多个列，因此 conkey pg_constraint 的和 confkey 是数组。您必须取消嵌套数组以获取列名或类型的列表。您可以使用以下函数：

A foreign key may be based on multiple columns, so conkey and confkey of pg_constraint are arrays. You have to unnest the arrays to get a list of column names or types. You can use these functions:

create or replace function get_col_names(rel regclass, cols int2[])
returns text language sql as $$
    select string_agg(attname, ', ' order by ordinality)
    from pg_attribute,
    unnest(cols) with ordinality
    where attrelid = rel
    and attnum = unnest
$$;

create or replace function get_col_types(rel regclass, cols int2[])
returns text language sql as $$
    select string_agg(typname, ', ' order by ordinality)
    from pg_attribute a
    join pg_type t on t.oid = atttypid,
    unnest(cols) with ordinality
    where attrelid = rel
    and attnum = unnest
$$;

查询约束和索引时，这些函数可能非常方便。您的查询对他们很简单：

The functions may be very handy when querying constraints and indexes. Your query is nice and simple with them:

select 
    conrelid::regclass,
    get_col_names(conrelid, conkey) col_names,
    get_col_types(conrelid, conkey) col_types,
    conname
from pg_constraint
where contype ='f';

 conrelid | col_names | col_types |        conname         
----------+-----------+-----------+------------------------
 products | image_id  | int4      | products_image_id_fkey
(1 row)

这篇关于列出所有外键PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！