postgresql 如何SELECT引用同一列作为外键的所有(表、列

lsmd5eda 于 2023-01-13 发布在 PostgreSQL

关注(0)|答案(2)|浏览(136)

(TL;DR位于末尾）
我正在合并两个大小合适的postgres数据库。由于存在ID冲突和许多外键，我希望UPDATE foo SET bar_id = bar_id + 100000 CASCADE是SQL中的一个东西，所以它会神奇地相应地更新所有内容。不幸的是，它不是。
所以我想使用一个LOOP结构（见下文），它可以简单地编辑所有引用。我想使用一个选择查询，返回所有引用了我想要的列的table_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”、"foo_storageroom“、”foo_lastrecomposition_place“、”operating_theatre“等等。有列从位置类型表中引用“位置类型_id”，这些列是LIKE '%place%'，我不想更改位置类型_id，因此我们无法仅从名称中猜测要包括或不包括哪一列。
我知道有information_schema.table_constraints表，但它没有告诉引用的列。如果我们可以从约束名称中获得定义，则可能匹配：
ILIKE format('%%REFERENCES %s(%s)%%',table_name,column_name)
但是这个定义也不是table_constraints表的一部分。
(For那些想知道的人，我正在研究与消毒服务相关的医院数据库。）

我想要什么/ TL;德国

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

postgresql

来源：https://stackoverflow.com/questions/67617388/how-do-i-select-all-table-column-referencing-the-same-column-as-foreign-key

2条答案

按热度按时间

5n0oy7gb1#

好吧，所以我已经做到了：-）
以下查询返回数据库中引用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;

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

--------------其他答案------------------
我通过改进@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');

Test it there
我不认为性能在这里真的很重要，所以应该考虑哪些是次要的需求。我认为我的第一个解决方案的优点是，如果您想更改约束定义（例如，添加一个ON UPDATE CASCADE子句），那么第二个解决方案似乎更“紧凑”，因为它只返回table. column。

赞(0）回复(0）举报 2023-01-13

yizd12fk2#

这也许太长了，不宜评论。
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中。Here是db〈〉小提琴。

赞(0）回复(0）举报 2023-01-13

我来回答

postgresql 如何SELECT引用同一列作为外键的所有(表、列

2条答案

相关问题

热门标签

最新问答