Динамические SQL-запросы в PostgreSQL: когда, зачем и как

Сегодня поговорим о мощной штуке в PostgreSQL, которая одновременно помогает и открывает портал в ад: динамические SQL‑запросы. Динамика — это когда SQL собирается на лету, а не пишется заранее статичным текстом. Звучит неплохо, но при неправильном подходе легко превращается в катастрофу.

Зачем вообще нужны динамические SQL-запросы?

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

Основные кейсы, где динамика необходима:

1. Динамические таблицы

У вас есть схема, где данные для каждого клиента лежат в своей таблице (я знаю, кто‑то скажет, что это плохо, но бизнес так захотел). Например, data_client_1, data_client_2 и так далее. И вот вам нужно доставать оттуда данные, зная название таблицы только во время выполнения. Без динамического SQL это может быть тяжело.

2. Гибкие фильтры

Ваш пользователь хочет фильтровать данные по куче полей, но эти поля каждый раз разные. Либо вы пишете статический запрос с тонной CASE WHEN, либо генерируете динамический SQL. Второе часто проще.

3. Административные задачи

Миграции, массовое обновление данных, построение индексов или удаление таблиц в цикле — всё это задачи, где динамика незаменима.

4. Производительность

Иногда динамический SQL позволяет заранее вычислить план выполнения, сэкономить на ненужных джойнах или подгрузить таблицы, которые меняются в зависимости от ситуации.

В чём опасность?

Если вы пишете динамические запросы бездумно, рискуете столкнуться с:

SQL‑инъекциями
Классика. Динамический SQL любит ломаться, когда пользователь умышленно или случайно подсовывает кривые данные.
Проблемы с производительностью
Динамика ломает кеширование планов выполнения. Если запрос каждый раз уникален, PostgreSQL вынужден заново строить план — а это накладно.
Читаемость кода
Сложные динамические запросы превращаются в мешанину из строк, условий и форматирования. Через месяц вы сами не поймёте, что тут происходит.

Как работает динамический SQL в PostgreSQL?

Основные инструменты:

EXECUTE — выполняет строку как SQL‑запрос.
FORMAT — помогает безопасно собирать строку.
%I и %L — форматирование для экранирования идентификаторов и литералов соответственно.

Пример: безопасный динамический запрос.

DO $$ DECLARE     table_name TEXT := 'users';     column_name TEXT := 'email'; BEGIN     EXECUTE FORMAT('SELECT %I FROM %I', column_name, table_name); END $$;

Здесь %I защищает нас от инъекций, правильно экранируя имена таблиц и колонок.

Помимо этого существует:

RAISE NOTICE
С его помощью можно вывести текст запроса перед выполнением, чтобы понять, что именно собирается выполнить EXECUTE.
```
DO $$ DECLARE     table_name TEXT := 'users';     query TEXT; BEGIN     query := FORMAT('SELECT * FROM %I', table_name);     RAISE NOTICE 'Executing query: %', query;     EXECUTE query; END $$;
```
PREPARE и EXECUTE (не путать с EXECUTE внутри PL/pgSQL)
Инструмент для подготовки запросов на уровне SQL, а не PL/pgSQL. Можно подготовить запрос с параметрами и выполнять его несколько раз с разными значениями. Подходит для случаев, где нужно сэкономить на планировании.
```
PREPARE dynamic_query(TEXT) AS SELECT * FROM users WHERE email = $1; EXECUTE dynamic_query('kotik@catmail.com');
```
USING в EXECUTE
Альтернатива FORMAT, если хочется избегать конкатенации строк для подстановки параметров.
```
DO $$ DECLARE     query TEXT;     param TEXT := 'example@example.com'; BEGIN     query := 'SELECT * FROM users WHERE email = $1';     EXECUTE query USING param; END $$;
```
USING автоматически экранирует данные, так что SQL-инъекции тут тоже не страшны.

Примеры использования

Чтение данных из динамической таблицы

Представьте, что есть несколько таблиц с данными клиентов: data_client_1, data_client_2. Пишем функцию для выборки данных.

CREATE OR REPLACE FUNCTION get_client_data(table_name TEXT) RETURNS TABLE(id INT, value TEXT) AS $$ BEGIN     RETURN QUERY EXECUTE FORMAT('SELECT id, value FROM %I', table_name); END $$ LANGUAGE plpgsql;

Используем:

SELECT * FROM get_client_data('data_client_1');

Экранируем имя таблицы через %I. Никто не сможет сломать запрос.

Динамические фильтры

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

CREATE OR REPLACE FUNCTION get_filtered_data(start_date DATE, end_date DATE, status TEXT) RETURNS TABLE(id INT, created_at DATE, status TEXT) AS $$ BEGIN     RETURN QUERY EXECUTE FORMAT(         'SELECT id, created_at, status          FROM orders          WHERE created_at BETWEEN %L AND %L            AND status = %L',         start_date, end_date, status     ); END $$ LANGUAGE plpgsql;

Тестируем:

SELECT * FROM get_filtered_data('2024-01-01', '2024-12-31', 'active');

%L защищает литералы, добавляя кавычки и экранирование.

Массовое обновление данных

Допустим, есть список таблиц, в которых нужно обновить данные.

CREATE OR REPLACE FUNCTION update_multiple_tables(tables TEXT[], new_value TEXT) RETURNS VOID AS $$ DECLARE     table_name TEXT; BEGIN     FOREACH table_name IN ARRAY tables LOOP         EXECUTE FORMAT('UPDATE %I SET value = %L WHERE value IS NULL', table_name, new_value);     END LOOP; END $$ LANGUAGE plpgsql;

Вызываем:

SELECT update_multiple_tables(ARRAY['table1', 'table2'], 'default_value');

Динамическое создание индексов

Добавим индексы в несколько таблиц.

CREATE OR REPLACE FUNCTION create_indexes(tables TEXT[], column_name TEXT) RETURNS VOID AS $$ DECLARE     table_name TEXT; BEGIN     FOREACH table_name IN ARRAY tables LOOP         EXECUTE FORMAT('CREATE INDEX IF NOT EXISTS idx_%I_%I ON %I (%I)',                         table_name, column_name, table_name, column_name);     END LOOP; END $$ LANGUAGE plpgsql;

В итоге

Динамический SQL — мощный, но любит тех, кто к нему с головой: используйте FORMAT, экранируйте %I и %L, логируйте свои запросы — и все будет хорошо. Главное — не пытайтесь слепо склеивать строки, а то SQL-инъекции уже точат на вас зубы.

Делитесь своими кейсами использования динамического SQL в комментариях.

Сегодня, 28 ноября, в 20:00 пройдет открытый урок, посвященный использованию Foreign-Data Wrappers в PostgreSQL. Успевайте записаться, если интересно принять участие.

Больше про IT-инфраструктуру и не только эксперты OTUS рассказывают в рамках практических онлайн-курсов. С полным каталогом курсов можно ознакомиться по ссылке.

ссылка на оригинал статьи https://habr.com/ru/articles/861240/