PostgreSQL | Как подставить значение имени таблицы при её создании?

PostgreSQL | Как подставить значение имени таблицы при её создании?

Как при помощи функции в PostgreSQL создавать таблицы с динамическими именами, которые берут значения из переменных или передаваемых параметров в функцию?

Давайте для примера напишем небольшую функцию, которая создаёт самую простую таблицу из двух столбцов, где имя будущей таблицы мы передаём в качестве параметра функции.

Мы планируем создавать много таких таблиц автоматически, при изменении остальных участков СУБД.

В процедурном языке «PL/pgSQL«, есть специальный оператор, который позволяет выполнять SQL-запрос, оформленный в виде строки.

Он называется EXECUTE и относится к разделу «Выполнение динамически формируемых команд» официальной документации.

 

Функция, создающая таблицу с нужным именем, переданным в качестве параметра - PostgreSQL
Функция, создающая таблицу с нужным именем, переданным в качестве параметра — PostgreSQL

 

Код функции для создания таблицы с нужным именем

— Создание отдельной таблицы с нужным именем

— DROP FUNCTION IF EXISTS test1.add_newtable(idtable bigint);

 

CREATE OR REPLACE FUNCTION test1.add_newtable(idtable bigint)

    RETURNS bigint

    LANGUAGE ‘plpgsql’

AS $BODY$

    BEGIN

    EXECUTE ‘CREATE TABLE IF NOT EXISTS test1.table_’||idtable||

        (

           id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (

               INCREMENT 1

               START 1

               MINVALUE 1

               MAXVALUE 9223372036854775807

               CACHE 1

           ),

           data text NOT NULL,

           CONSTRAINT table_’

           ||idtable||‘_pkey PRIMARY KEY (id),

           CONSTRAINT table_’

           ||idtable||‘_data_key UNIQUE (data)

        )’;

    RETURN idtable;

    END;

$BODY$;

 

В этой функции параметр idtable — это уникальное значение для будущей идентификации таблицы по её имени. То есть идея в том, чтобы генерировать таблицы одного вида:

  1. table_1
  2. table_2
  3. table_789
  4. и так далее

После выполнения, функция возвращает идентификатор обратно. Это может пригодиться для более сложных вычислений, когда нужно дальше пробрасывать уникальную часть имени таблицы.

Ключевой момент ПОДСТАНОВКИ ИМЕНИ значения из переменной заключается в конструкции вида:

||idtable||

Это выражение представляет собой как-бы разрыв строки, говоря среде выполнения кода, что в этом участке между двумя двойными вертикальными линиями нужно написать переменную, из которой будет ВЫТАЩЕНО и ПОДСТАВЛЕНО нужное значение.

Тем кто знаком с JavaScript это очень напоминает ЛИТЕРАЛ ШАБЛОНА. Оформляется он в виде обратных одинарных кавычек `bla bla${idtable}bla bla`.

В конструкции EXECUTE числовой параметр будет приведён к строковому для оформления итоговой строки SQL-запроса.

 

Вызов функции с уникальным именем для создания таблицы

Чтобы вызвать нашу функцию, нужно выполнить такой SQL-запрос:

SELECT test1.add_newtable(55);
Успешно вызвали функцию и создали новую таблицу с нужным именем - PostgreSQL
Успешно вызвали функцию и создали новую таблицу с нужным именем — PostgreSQL

После вызова функции add_newtable() с параметром 55, мы получаем новую таблицу с именем table_55

Это имя создалось динамически т. к. мы не говорили полностью как таблица должна называться. У такого подхода есть свой нюансы, поэтому читайте документацию. Мы передавали ЧИСЛО и это практически безопасно. Но если функция будет принимать СТРОКУ(текст), то это может стать местом уязвимости.

 

И можем вывести состав таблицы:

SELECT * FROM test1.table_55;
Получили все данные из таблицы, которая была создана с присвоением нужного имени - PostgreSQL
Получили все данные из таблицы, которая была создана с присвоением нужного имени — PostgreSQL

Как мы видим таблица успешно возвращает данные (которых пока нет). С таблицей всё в порядке — она действительно существует. Просто в ней пока нет добавленных записей.

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Раздел «43.5.4. Выполнение динамически формируемых команд» — https://postgrespro.ru/docs/postgresql/14/plpgsql-statements#PLPGSQL-STATEMENTS-EXECUTING-DYN

Раздел «9.4. Строковые функции и операторы» — https://postgrespro.ru/docs/postgresql/14/functions-string