PostgreSQL:如何运行从SQL SELECT语句返回的ALTER查询

Question

我想为所有表添加一个新列，表名模式为table_& lt;在_details.

我使用这个查询:

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