PostgreSQL : How to run ALTER queries returned as a result from SQL SELECT statement

Question

我想向所有表名模式为 的表添加一个新列表_ <> _详情 .
我使用这个查询:

 select 'alter table ' || table_name || ' ADD COLUMN CREATED TIMESTAMP;'
 from information_schema.tables
 where table_name like 'table_%_details';

生成如下所示的 DDL 查询:

alter table table_1_details ADD COLUMN CREATED TIMESTAMP;
alter table table_2_details ADD COLUMN CREATED TIMESTAMP;
alter table table_3_details ADD COLUMN CREATED TIMESTAMP;
alter table table_4_details ADD COLUMN CREATED TIMESTAMP;
alter table table_5_details ADD COLUMN CREATED TIMESTAMP;
alter table table_6_details ADD COLUMN CREATED TIMESTAMP;
alter table table_7_details ADD COLUMN CREATED TIMESTAMP;

我尝试使用以下脚本遍历这些记录:

do $$ declare c_query cursor for
select
    'alter table ' || table_name || ' ADD COLUMN CREATED TIMESTAMP;'
from
    information_schema.tables
where
    table_name like 'table_%_details';

begin
 for rec in c_query loop
 execute rec;
end loop;

close c_query;
end $$

我试图微调这个脚本，但没有成功，我收到以下错误:

SQL Error [42601]: ERROR: syntax error at or near ""alter table table_1_details ADD COLUMN CREATED TIMESTAMP;""
  Where: PL/pgSQL function inline_code_block line 13 at EXECUTE statement

我的问题是如何修改此脚本以遍历所有这些结果并将 DDL 应用于数据库，注意(我不想创建函数)。
请任何想法将不胜感激。

Answer 1

只需遍历 infomation_schema.tables 的结果集然后使用 EXECUTE与您的串联 ALTER TABLE声明

DO $$
DECLARE
  row record;
BEGIN
  FOR row IN SELECT table_name FROM information_schema.tables
             WHERE table_name LIKE 'table_%_details' LOOP
    EXECUTE 'ALTER TABLE ' || row.table_name || ' ADD COLUMN CREATED TIMESTAMP;';
  END LOOP;
END;
$$;

编辑 : 或者你可以使用 FORMAT连接你的字符串而不是使用 || ，正如@a_horse_with_no_name 所指出的

EXECUTE FORMAT('ALTER TABLE %I ADD COLUMN CREATED TIMESTAMP;',row.table_name);

检查这个 db<>fiddle