向 PostgreSQL 数据库的所有架构中的表添加列

我有一个 Postgres 8.4 架构，如下所示：

My_Database
 |-> Schemas
       |-> AccountA
       |-> AccountB
       |-> AccountC
       |-> AccountD
       |-> AccountE
      ...
       |-> AccountZ

所有模式都有一个名为的表product我想立即向它们添加一个布尔列。是否有可能做到这一点？

到目前为止我发现的唯一方法是按帐户运行以下 SQL 帐户。

ALTER TABLE product ADD COLUMN show_price boolean NOT NULL DEFAULT TRUE;

DO
$do$
DECLARE
  _schema text;
  _sp
BEGIN
   FOR _schema IN
      SELECT quote_ident(nspname)  -- prevent SQL injection
      FROM   pg_namespace n
      WHERE  nspname !~~ 'pg_%'
      AND    nspname <>  'information_schema'
   LOOP
      EXECUTE 'SET LOCAL search_path = ' || _schema;
      ALTER TABLE product ADD COLUMN show_price boolean NOT NULL DEFAULT TRUE;
   END LOOP;
END
$do$

您可以使用以下命令循环遍历系统目录表中的条目DO陈述 https://www.postgresql.org/docs/current/sql-do.html。需要Postgres 9.0 或更高版本.
你也可以创建一个函数 https://www.postgresql.org/docs/current/sql-createfunction.html. The DO声明使用过程语言plpgsql https://www.postgresql.org/docs/current/plpgsql.html默认情况下。

您需要的唯一系统目录是pg_namespace，保存数据库的模式。循环遍历除已知系统模式之外的所有模式。

确保您连接到正确的数据库！

向表中添加一列NOT NULL约束，您还必须提供默认值来填充新列。否则逻辑上不可能。我添加了DEFAULT TRUE，根据您的需要进行调整。

通过正确引用从系统目录表检索的标识符来避免 SQL 注入。quote_ident()在这种情况下。 [还有更多选择。看：

• Postgres 函数中的 SQL 注入与准备好的查询 https://dba.stackexchange.com/a/49718/3684

您需要动态 SQL。主要的“技巧”是设置search_path https://stackoverflow.com/questions/9067335/how-to-create-table-inside-specific-schema-by-default-in-postgres/9067777#9067777动态地，因此可以一遍又一遍地运行相同的语句。的效果SET LOCAL https://www.postgresql.org/docs/current/sql-set.html持续到交易结束。您可以使用RESET search_path或者如果您需要在同一事务中使用它执行更多操作（不太可能），请保存先前的状态并重置它：

SHOW search_path INTO _text_var;
...
EXECUTE 'SET search_path = ' || _text_var;