本文介绍了列出所有外键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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!