postgresql - 针对多个模式的 Postgres run 语句

Question

我有一个 Multi-Tenancy 应用程序，其中租户是在同一数据库内的不同模式上设置的。原因是他们在其中一个模式上使用了一些共享数据。

到目前为止，我一直在使用一个 bash 脚本，其中包含一个架构列表，每当添加新架构时都需要更新该脚本，并且我需要执行跨帐户的表架构更改等操作。例如向表中添加一个新列。

有没有办法在 Postgres、psql 等中运行...例如

ALTER TABLE some_table ADD COLUMN some_column TEXT NOT NULL DEFAULT '';

无需在另一个脚本(例如 bash)中进行字符串替换。

换句话说，是否有一种足够简单的方法来获取模式，并在 psql 中编写一个 for 循环，该循环将遍历模式并通过设置 search_path 来运行每个语句。

原因是租户数量在增长，并且新租户可以由非开发人员的管理员用户添加，所以我不断更新我的 shell 脚本。这只会呈指数增长。是否有处理此类问题的标准方法？

Answer 1

你可以用一个小的 PL/pgSQL block 来做到这一点:

do
$$
declare
  s_rec record;
begin
  for s_rec in select schema_name 
               from information_schema.schemata
               where schema_name not in ('pg_catalog', 'information_schema')
  loop
     execute format ('ALTER TABLE if exists %I.some_table ADD COLUMN some_column TEXT NOT NULL DEFAULT ''''), s_rec.schema_name);
  end loop;
end;
$$

if exists 将确保如果该表在架构中不存在，则语句不会失败。

---

如果您过度简化了您的问题并且实际上想要为每个模式运行一次完整的脚本，那么为每个模式生成一个包含实际脚本的脚本可能会更容易:

select concat(format('set search_path = %I;', schema_name), 
              chr(10), 
              '\i complete_migration_script.sql')
from information_schema.schemata
where schema_name not in ('pg_catalog', 'information_schema')

您可以将该语句的输出假脱机到一个文件中，然后使用 psql 运行该文件(当然您需要将 complete_migration_script.sql 替换为你的脚本)