如何在 Postgres 中一次更新多個表？

我有幾個結構完全相同的表，我需要更新所有表中的值。

為此，我嘗試建構以下腳本：

DO
$do$
DECLARE
 i pg_tables%rowtype;
BEGIN
FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
LOOP
   UPDATE i.tablename SET name = replace(name, '.js', '.ts');
END LOOP;
END
$do$;

我可以看到它i.tablename具有正確的值（我插入了一個 tmp 表進行檢查），但更新失敗。

name: error
length: 223
severity: ERROR
code: 42P01
internalPosition: 8
internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts')
where: PL/pgSQL function inline_code_block line 7 at SQL statement
file: parse_relation.c
line: 965
routine: parserOpenTable

i.tablename只是聲明上的外掛UPDATE不起作用。

有沒有辦法讓它工作？或者更簡單的方法來一次更新所有表？

你實際上很接近..首先創建一些測試數據..

CREATE TABLE foo_knex_migrations ( name )
 AS VALUES ('test.js'),('test2.js'),('bicycles');
CREATE TABLE bar_knex_migrations AS TABLE foo_knex_migrations;
CREATE TABLE baz_knex_migrations AS TABLE foo_knex_migrations;

接下來，我們將使用EXECUTE...FORMAT(), with %I。

DO
$do$
 DECLARE
   i pg_tables%rowtype;
 BEGIN
 FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
 LOOP
   EXECUTE FORMAT(
     $$
       UPDATE %I
       SET name = replace(name, '.js', '.ts');
     $$,
     i.tablename
   );
 END LOOP;
 END
$do$;


TABLE baz_knex_migrations ;
  name   
----------
test.ts
test2.ts
bicycles
(3 rows)

test=# TABLE foo_knex_migrations ;
  name   
----------
test.ts
test2.ts
bicycles
(3 rows)

作為旁注，

1. 一般來說，你應該使用information_schema標準化的東西來處理像這樣的簡單事情，而速度並不重要。
2. 您可能應該通過添加子句來檢查是否UPDATE需要執行。WHERE否則，您將一勞永逸地重寫表格。
3. 在 SQL 中，我們不使用這樣的命名約定。沒有必要這樣做。如果knex_migrations有多個表，請考慮CREATE SCHEMA knex_migrations儲存它們，而不是根據所有表的命名約定來搜尋目錄。

引用自：https://dba.stackexchange.com/questions/174439