你能帮我解决这个问题吗？ [英] Can you help me with this query?
问题描述
下面的查询将返回当前架构中的所有外键，
我将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：订阅和取消订阅命令转到 ma ***** **@postgresql.org
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
推荐答案
mi ********** @ mygenerationsoftware.com 写道：
下面的查询将返回当前架构中的所有外键，
我将ForeignKey名称作为FK_NAME以及主要和外来表格的名称和架构，现在我只需要在外键本身涉及的列中，pg_constraint中有一个名为confkey的列，它是一个数组？它保存了列id
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
这是一个数组，因为外键可以有多个参与的
字段 - 你怎么想这个代表？这里有一个方法，你可以得到一个参与字段名称的数组，假设你正在使用
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 =
1
和attnum = any（
1
and attnum = any (
2）） />
''语言sql;
SELECT cl.relname为TABLE_NAME，
cr.relname为FK_TABLE_NAME，
getattnames（ct.conrelid，ct.conkey）为TBL_ATTS，
getattnames（ct.confrelid，ct.confkey）为FK_TBL_ATTS
来自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'';
table_name | fk_table_name | tbl_atts | fk_tbl_atts
-------------------- + -------------------- + ------------- + -------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a，id3b} | {id1a，id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a，id3c} | {id2a，id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
（4行）
HTH，
Joe
---------------------------（播出结束）-------------- -------------
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
2))
'' language sql;
SELECT cl.relname as TABLE_NAME,
cr.relname as FK_TABLE_NAME,
getattnames(ct.conrelid, ct.conkey) as TBL_ATTS,
getattnames(ct.confrelid, ct.confkey) as FK_TBL_ATTS
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'';
table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)
HTH,
Joe
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
这篇关于你能帮我解决这个问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
