问题描述
下面的查询将返回当前架构中的所有外键，
我将ForeignKey名称作为FK_NAME以及主要和外国
表格''的名称和架构，现在我只需要
外键本身所涉及的列，pg_constraint中有一个名为confkey的列，而
它是一个数组？它包含列ID
SELECT ct.oid，conname为FK_NAME，confkey，nl.nspname为PK_TABLE_SCHEMA，
cl.relname为PK_TABLE_NAME，
nr.nspname为FK_TABLE_SCHEMA，cr.relname为FK_TABLE_NAME，描述
来自pg_constraint ct
JOIN pg_class cl ON cl.oid = conrelid
JOIN pg_namespace nl ON nl.oid = cl.relnamespace
JOIN pg_class cr ON cr.oid = confrelid
JOIN pg_namespace nr ON nr.oid = cr.relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid = ct.oid
WHERE contype =''f''
--- ------------------------（播出结束）--------------------- ------
提示1：订阅和取消订阅命令转到
The query below will return all of the foreign keys in the current schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table''s name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint and
it''s an array? It holds the column id
SELECT ct.oid, conname as FK_NAME, confkey, nl.nspname as PK_TABLE_SCHEMA,
cl.relname as PK_TABLE_NAME,
nr.nspname as FK_TABLE_SCHEMA, cr.relname as FK_TABLE_NAME, description
FROM pg_constraint ct
JOIN pg_class cl ON cl.oid=conrelid
JOIN pg_namespace nl ON nl.oid=cl.relnamespace
JOIN pg_class cr ON cr.oid=confrelid
JOIN pg_namespace nr ON nr.oid=cr.relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
WHERE contype=''f''
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
推荐答案
这是一个数组，因为外键可以有多个参与的
字段 - 你怎么想这个代表？这里有一个方法，你可以得到一个参与字段名称的数组，假设你正在使用
Postgres 7.4.x：
创建或替换函数getattnames（oid，smallint []）
将name []返回为''
select array（从pg_attribute中选择attname
其中attrelid =
It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here''s a way that you can get
an array of the participating field names, assuming you''re using
Postgres 7.4.x:
create or replace function getattnames(oid, smallint[])
returns name[] as ''
select array(select attname from pg_attribute
where attrelid =
这篇关于你能帮我解决这个问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！