Postgres - 使用单个查询更新多个表中的系统列

Question

我想在单个查询中更新所有表中的sysOperatorId列。

update (SELECT
tablename
FROM
pg_catalog.pg_tables
WHERE
(schemaname != 'pg_catalog' AND schemaname != 'information_schema') order by tablename) 
set sysOperatorId = 'xxxxxx-xxx-xxxxx' where sysOperatorId = 'xxxxxx-xxx-xxxxx'

上面的子查询返回用户创建的表。在每个表中，我都有一个列 sysOperatorId，我想更新该列。

Answer 1

为此，您需要动态 SQL。

类似于以下内容(未经测试(：

do
$$
declare
l_sql text;
l_rec record;
l_id1 text := 'xxxxxx-xxx-xxxxx';
l_id2 text := 'xxxxxx-xxx-xxxxx';
begin
for l_rec in select tablename 
from pg_catalog.tables
where schemaname not in ('pg_catalog', 'information_schema')
loop
l_sql := format('update %I set sysoperatorid = $1 where sysoperatorid = $2', l_rec.tablename);
execute l_sql 
using l_id1, l_id2;
end loop;
end;
$$

format()函数使用pg_tables.tablename中的值作为目标表生成 UPDATE 语句。I%是标识符的占位符，将正确处理需要引用的表。

然后执行此 SQL 字符串，将两个 ID 作为参数传递(而不是将它们作为常量包含在 SQL 字符串中(。