Генерация кода с помощью запросов в PostgreSQL

PostgreSQLНа днях у меня спросили, как правильно написать цикл в PL/pgSQL, чтобы на выходе получилась генерация нужного количества SQL-команд вида: «CREATE TABLE tableN …».

[все мысли пролетали в голове очень быстро, этот тут писать долго получилось] Первой мыслью было — раз спрашивают цикл, надо вспомнить цикл. А потом — нужен же результат, а результат можно получить гораздо проще, использоваться SQL-запорс, а не процедурный подход.

В результате получилось то, что ниже. Понятное дело, всё это понятно для понятливых и для тех, кто это уже делал. Я это уже давно не делал, так что разольюсь мыслью и опишу, как получить нужный код из SQL-запроса.

Ранее, на первой своей работе, я такое уже делал — генератор кода для программы на C# с MVVM и WPF. Менял, например, несколько полей в таблицах проекта (на стадии создания прототипа), запускал скрипт перегенерации кода, копировал полученный код, вставлял в проект в Visual Studio (полностью заменял прошлый код), сохранял и запускал — всё круто. В таком генераторе внутри СУБД можно много разных проверок натолкать.

Так вот. Чтобы получить нужное количество нужного кода, нужно воспользоваться функцией generate_series (более подробно про функции можно почитать в документации — Функции, возвращающие множества). Данная функция возвращает множество строк, в зависимости от переданных параметров.

В SQL-запросе не обязательно вытаскивать данные из таблицы, можно FROM и далее не писать. А нужный код, который должен быть статическим (в моем примере — «CREATE TABLE table») пропишем в блоке перечисления полей таблицы следующим образом:

postgres=# SELECT 'CREATE TABLE table' ;
      ?column?      
--------------------
 CREATE TABLE table
(1 row)

Теперь добавим сюда функцию generate_series, результат которой будем конкатенировать (две прямых черты) с постоянной частью запроса. Нам ведь нужно, например, создать десять таблиц с индексами от 1 до 10:

postgres=# SELECT 'CREATE TABLE table' || generate_series(1,10);
       ?column?       
----------------------
 CREATE TABLE table1
 CREATE TABLE table2
 CREATE TABLE table3
 CREATE TABLE table4
 CREATE TABLE table5
 CREATE TABLE table6
 CREATE TABLE table7
 CREATE TABLE table8
 CREATE TABLE table9
 CREATE TABLE table10
(10 rows)

Как раз то, что нужно. Осталось только дописать справа структуру таблицы:

postgres=# SELECT 'CREATE TABLE table' || generate_series(1,10) || ' (id integer);';
              ?column?              
------------------------------------
 CREATE TABLE table1 (id integer);
 CREATE TABLE table2 (id integer);
 CREATE TABLE table3 (id integer);
 CREATE TABLE table4 (id integer);
 CREATE TABLE table5 (id integer);
 CREATE TABLE table6 (id integer);
 CREATE TABLE table7 (id integer);
 CREATE TABLE table8 (id integer);
 CREATE TABLE table9 (id integer);
 CREATE TABLE table10 (id integer);
(10 rows)

Вроде бы всё. Но мысль дальше побежала — нужно же этот код далее использовать. Вдруг требуется создать 10 000 таблиц? Неудобно же будет вывод копировать руками, куда-то там добавлять и т. д.

Всё можно сделать своими «руками» и psql`ом. В этом клиенте можно результат запроса записать в файл. А потом этот же файл скормить этому же psql`y.

Одно только НО есть — обратите внимание, в результате работы запроса отображается заголовок (название столбцов) и количество полученных строк. Нам это совсем не нужно (в результирующем скрипте). И в psql`e это можно отключить с помощью команды \t (подробнее про многоразнообразные команды данного клиента читайте в документации — pqsl):

postgres=# \t
Tuples only is on.
postgres=# select 1;
        1

Нет названия столбцов и кол-во строк.

Теперь сохраним результат запроса во внешний файл. Для этого включим данную возможность командой \o название_файла:

postgres=# \o sql_create_table
postgres=# SELECT 'CREATE TABLE table' || generate_series(1,10) || ' (id integer);';
postgres=#

Никакого вывода результата мы не увидим — всё уходит в указанный файл. Если еще какой-то запрос выполнить — его результат будет дописан в файл. Чтобы отключить эту возможность — выполняем команду \o.

Если снова включить перенаправление вывода результата в тот же файл и запустить запрос — файл будет перезаписан.

Посмотрим, что у нас получилось. Можно открыть этот файл в другом терминале, а можно запустить команду операционной системы прямо из psql`a (да-да, документация) c помощью \!:

postgres=# \! cat sql_create_table
 CREATE TABLE table1 (id integer);
 CREATE TABLE table2 (id integer);
 CREATE TABLE table3 (id integer);
 CREATE TABLE table4 (id integer);
 CREATE TABLE table5 (id integer);
 CREATE TABLE table6 (id integer);
 CREATE TABLE table7 (id integer);
 CREATE TABLE table8 (id integer);
 CREATE TABLE table9 (id integer);
 CREATE TABLE table10 (id integer);

Содержимое файла (меня) полностью удовлетворяет. Теперь осталось этот код запустить. И снова никуда не нужно ходить — с помощью всё той же команды \! скармливаем содержимое нашего файла psql`y:

postgres=# \! psql знак_больше sql_create_table
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

И посмотрим созданные таблицы командой \d:

postgres=# \d
 public | table1  | table | postgres
 public | table10 | table | postgres
 public | table2  | table | postgres
 public | table3  | table | postgres
 public | table4  | table | postgres
 public | table5  | table | postgres
 public | table6  | table | postgres
 public | table7  | table | postgres
 public | table8  | table | postgres
 public | table9  | table | postgres

Что и нужно было — SQL-скриптом сгенерировали нужное количество команд создания таблиц, записали в файл, выполнили этот файл — получили таблицы. Можно еще написать скрипт удаления этих таблиц по тому же алгоритму.

Попробовал обойтись без использования стороннего файла, чтобы сразу и сгенерировать и выполнить полученный код. Сходу не получилось.

Как подсказали, нужно использовать \gexec (спасибо 🙂 ) Да, всё просто. Нужно сделать вот так:

postgres=# SELECT 'CREATE TABLE table' || generate_series(11,12) || ' (id integer);' \gexec
CREATE TABLE
CREATE TABLE

Еще проще. Никаких файлов и то, что нужно (но скрипты во внешних файлах тоже иногда нужны).


2 Comments on Генерация кода с помощью запросов в PostgreSQL

Leave a Reply

Ваш Mail не будет опубликован.


*