Добавить столбец в таблицу во всех схемах базы данных PostgreSQL

Question

У меня есть схема 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;

Answer 1

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$

Основные моменты

• Как уже объяснял @Denis, вы можете просматривать записи в таблицах системного каталога с помощью DO заявление. Однако для этого требуется Postgres 9.0 или более поздняя версия. Вы также можете создать функцию (работает также в 8.4). DO В выражении по умолчанию используется процедурный язык plpgsql.

• Единственный системный каталог, который вам нужен pg_namespace, содержащий схемы базы данных. Переберите все схемы, кроме известных системных схем.

• Убедитесь, что вы подключены к правильной базе данных!

• Чтобы добавить столбец в таблицу с NOT NULL ограничение, вы также должны предоставить значение по умолчанию для заполнения новых столбцов. Логически невозможно, по-другому. я добавил DEFAULT TRUE на ваше заявление. Подстраивайся под свои нужды.

• Избегайте SQL-инъекций, правильно цитируя идентификаторы, полученные из таблиц системного каталога форм. quote_ident() в этом случае. Есть еще варианты (связанный ответ на dba.SE).

• Вам нужен динамический SQL. Мой основной трюк состоит в том, чтобы просто установить search_path динамически, поэтому один и тот же оператор может выполняться снова и снова. Эффект SET LOCAL длится до конца сделки. Ты можешь использовать RESET search_path или сохраните предыдущее состояние и сбросьте его, если вам нужно сделать больше в той же транзакции с ним (маловероятно):

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

Answer 2

Вам нужно будет сделать это в любом случае.

Что вы можете сделать, это автоматизировать его, запросив каталог, а затем обернуть код в do блок (в Postgres 9.0 или более поздней версии) или с помощью сценария миграции. Чтобы найти нужный запрос, если вы не знакомы с запросами к каталогу, проще всего использовать psql -E, Он покажет скрытые запросы, которые запускаются при вводе \dn а также \dt,