在PostgreSQL数据库的所有方案中向表中添加列 [英] Add a column to a table in all schemas of a PostgreSQL database

问题描述

我有一个如下所示的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 statement遍历系统目录表中的条目。需要Postgres 9.0或更高版本。
您也可以create a function。默认情况下，DO语句使用procedural language plpgsql。

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

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

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

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

• SQL injection in Postgres functions vs prepared queries

您需要动态SQL。主要的诀窍是动态设置search_path，这样同一条语句就可以反复运行。SET LOCAL的影响一直持续到交易结束。您可以使用RESET search_path或保存以前的状态，如果您需要在同一事务中使用它执行更多操作(不太可能)，则将其重置：

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

这篇关于在PostgreSQL数据库的所有方案中向表中添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！