Давайте для примера напишем небольшую функцию, которая создаёт самую простую таблицу из двух столбцов, где имя будущей таблицы мы передаём в качестве параметра функции.
Мы планируем создавать много таких таблиц автоматически, при изменении остальных участков СУБД.
В процедурном языке «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 — это уникальное значение для будущей идентификации таблицы по её имени. То есть идея в том, чтобы генерировать таблицы одного вида:
После выполнения, функция возвращает идентификатор обратно. Это может пригодиться для более сложных вычислений, когда нужно дальше пробрасывать уникальную часть имени таблицы.
Ключевой момент ПОДСТАНОВКИ ИМЕНИ значения из переменной заключается в конструкции вида:
||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
При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…
-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…
Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…
Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…
У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…
Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…