如何选择所有引用同一列作为外键的(表、列)

Question

(TL；末尾为DR(

我正在合并两个大小合适的postgres数据库。由于存在ID冲突和许多外键，我很喜欢UPDATE foo SET bar_id = bar_id + 100000 CASCADE在SQL中的作用，所以它会神奇地相应地更新所有内容。不幸的是，事实并非如此。

因此，我想使用一个LOOP结构(见下文(，它将简单地编辑所有地方的引用。我想要一个选择查询，返回引用我想要的列的alltable_name，column_name。

DO
$$
DECLARE
rec record;
BEGIN
FOR rec IN 
(SELECT table_name, column_name FROM /*??*/ WHERE /*??*/)   -- <<< This line    
LOOP  
EXECUTE format('UPDATE %I SET %s = %s + 100000 ;',  
rec.table_name,rec.column_name,rec.column_name); 
END LOOP;
END;
$$
LANGUAGE plpgsql;

我已经知道如何获得所有具有特定column_name的表(+column_name(，当外键列与它引用的列共享名称时，我会使用该表。或者，即使是列名称列表，我也知道：

SELECT col.table_name, col.column_name
FROM information_schema.columns col 
right join
information_schema.tables tab 
ON col.table_name = tab.table_name 
WHERE column_name = 'foo_id' 
--  IN ('FOO_ID','BAR_FOO_ID') | or : like '%foo_id' | both works well most of the time
and tab.table_type = 'BASE TABLE' 

但是。。。我现在使用的是一个表PLACES，其中place_id列在至少60个不同的约束(匹配LIKE '%place_id'(上被引用然后有一些列引用了以其他方式命名的位置id，如"foo_currentplace"、"o_storageroom"、"foo_lastrecomposition_place"、"operating_theatre"等。另一方面，还有一些列引用的是位置类型表中的"placetype_id"，这些列是LIKE '%place%'，我不想更改位置类型_id，所以我们不能仅从它们的名称来猜测要包括或不包括哪一列。

我知道有information_schema.table_constraints表，但它没有告诉被引用的列。如果我们可以从约束名称中获得定义，则可以匹配：
ILIKE format('%%REFERENCES %s(%s)%%',table_name,column_name)
，但该定义也不属于table_constraints表。

(对于那些想知道的人，我正在研究与绝育服务相关的医院数据库。(

我想要什么/TL；DR

我需要一个SELECT查询(或函数定义(，返回整个数据库(schema_name,table_name,column_name)或(table_name,column_name)的所有列，这些列具有引用指定列(参数(的外键约束。

Answer 1

好的，所以我已经完成了：-(
以下查询返回数据库中引用FOO_TABLE.foo_column作为外键的每一列：

SELECT 
fk.table_schema as schema, --optional in my case, I only use public
fk.table_name as table,
substring(fk.constraint_def, 14, position(')' in constraint_def)-14) as column
FROM
(SELECT tc.*,
pg_get_constraintdef(c.oid) as constraint_def
--,c.oid 
from pg_constraint c
left join information_schema.table_constraints tc
on c.conname = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY')
as fk    
WHERE constraint_def ILIKE format('%%REFERENCES %s(%s)%%',
'FOO_TABLE','foo_column')  
ORDER BY table_schema,table_name,3;

在那里测试

我发现information_schema.table_constraints从pg_constraint获得了大部分信息，其中包括内部引用OID，并且有一个内置函数pg_get_constraintdef()从它的OID返回约束对象的定义
然后，定义的某个子字符串足以提取column_nameAND使用我在问题中准备的(I)LIKE过滤器过滤引用的列。

-----------------其他答案------------------

我通过改进@Abelisto建议构建的另一个可接受的查询：

SELECT table_name, column_name
FROM (SELECT table_name, SUBSTR(column_name, 2, LENGTH(column_name)-2) as column_name,
referenced_table,SUBSTR(referenced_column, 2, LENGTH(referenced_column)-2) as referenced_column
FROM(select
conrelid::regclass::text as table_name,
(select array_agg(attname) from pg_attribute where conrelid = attrelid and attnum = any(conkey))::text as column_name,
confrelid::regclass::text as referenced_table,
(select array_agg(attname) from pg_attribute where confrelid = attrelid and attnum = any(confkey))::text as referenced_column
from pg_constraint where contype = 'f' 
) b ) a
WHERE (referenced_table, referenced_column) = ('FOO_TABLE','foo_column');

在那里测试

我不认为表现真的很重要，所以我们应该选择球队需要什么。我认为，如果您想更改约束定义(例如，添加ON UPDATE CASCADE子句(，我的第一个解决方案具有获得约束定义的优势，但第二个解决方案似乎更"简单"；紧凑型"；正是为了返回table.column.

Answer 2

对于注释来说，这可能太长了。

Postgres绝对支持级联更新约束(请参阅此处(。但是，这是约束的定义的一部分，而不是update语句：

ALTER TABLE foo 
ADD FOREIGN KEY fk_foo_bar_id FOREIGN KEY (bar_id) REFERENCES bar(bar_id)
ON UPDATE CASCADE;
----^ this is the important part

注意：您需要重新定义所有外键约束，使其级联更新约束。

而且，作为编辑评论，我通常避免级联更新外键约束(因为我不喜欢主键更改值的想法(。然而，这绝对是一个他们有用的地方。

然后，如果更改bar.bar_id，则更改将反映在foo中。这里有一个db<gt；不停摆弄