Как при помощи функции в PostgreSQL создавать таблицы с динамическими именами, которые берут значения из переменных или передаваемых параметров в функцию?
Давайте для примера напишем небольшую функцию, которая создаёт самую простую таблицу из двух столбцов, где имя будущей таблицы мы передаём в качестве параметра функции.
Мы планируем создавать много таких таблиц автоматически, при изменении остальных участков СУБД.
В процедурном языке «PL/pgSQL«, есть специальный оператор, который позволяет выполнять SQL-запрос, оформленный в виде строки.
Он называется EXECUTE и относится к разделу «Выполнение динамически формируемых команд» официальной документации.
Код функции для создания таблицы с нужным именем
— Создание отдельной таблицы с нужным именем
— 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 — это уникальное значение для будущей идентификации таблицы по её имени. То есть идея в том, чтобы генерировать таблицы одного вида:
- table_1
- table_2
- …
- table_789
- и так далее
После выполнения, функция возвращает идентификатор обратно. Это может пригодиться для более сложных вычислений, когда нужно дальше пробрасывать уникальную часть имени таблицы.
Ключевой момент ПОДСТАНОВКИ ИМЕНИ значения из переменной заключается в конструкции вида:
||idtable||
Это выражение представляет собой как-бы разрыв строки, говоря среде выполнения кода, что в этом участке между двумя двойными вертикальными линиями нужно написать переменную, из которой будет ВЫТАЩЕНО и ПОДСТАВЛЕНО нужное значение.
Тем кто знаком с JavaScript это очень напоминает ЛИТЕРАЛ ШАБЛОНА. Оформляется он в виде обратных одинарных кавычек `bla bla${idtable}bla bla`.
В конструкции EXECUTE числовой параметр будет приведён к строковому для оформления итоговой строки SQL-запроса.
Вызов функции с уникальным именем для создания таблицы
Чтобы вызвать нашу функцию, нужно выполнить такой SQL-запрос:
SELECT test1.add_newtable(55);
После вызова функции add_newtable() с параметром 55, мы получаем новую таблицу с именем table_55
Это имя создалось динамически т. к. мы не говорили полностью как таблица должна называться. У такого подхода есть свой нюансы, поэтому читайте документацию. Мы передавали ЧИСЛО и это практически безопасно. Но если функция будет принимать СТРОКУ(текст), то это может стать местом уязвимости.
И можем вывести состав таблицы:
SELECT * FROM test1.table_55;
Как мы видим таблица успешно возвращает данные (которых пока нет). С таблицей всё в порядке — она действительно существует. Просто в ней пока нет добавленных записей.
Информационные ссылки
Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org
Официальный сайт СУБД PostgreSQL — https://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
