PostgreSQL:更改select查询返回的所有表

Question

我在postgres 11.2 db:上运行了这个查询

SELECT relname
FROM pg_stat_user_tables
WHERE n_mod_since_analyze=0 AND relname LIKE '%' 
AND relname NOT IN (SELECT relname FROM pg_class WHERE  array_length(reloptions,1)>0);

这将返回一个我需要禁用autovacuum的表列表。我正试图想出一种方法，在查询语句返回的所有表上运行ALTER TABLE pg_stat_user_tables.relname SET (autovacuum_enabled = false);。谷歌搜索让我又回到了更改表列的问题上。是否可以只获取从该查询返回的表名并将其传递给ALTERTABLE？

Answer 1

尝试使用psql CLI和\gexec内部命令：

SELECT format('ALTER TABLE %s SET (autovacuum_enabled = false);',lt.relname)
FROM
(
SELECT relname
FROM pg_stat_user_tables
WHERE n_mod_since_analyze=0 
AND relname LIKE '%' 
AND relname NOT IN (SELECT relname FROM pg_class WHERE  array_length(reloptions,1)>0)
) lt;
gexec

执行输出：

ALTER TABLE p SET (autovacuum_enabled = false);
ALTER TABLE
ALTER TABLE t1 SET (autovacuum_enabled = false);
ALTER TABLE
ALTER TABLE t2 SET (autovacuum_enabled = false);
ALTER TABLE