Динамический SQL в PostgreSQL: EXECUTE и строим запросы программно

Иногда заранее неизвестно, как будет выглядеть запрос: какие фильтры выберет пользователь, к какой таблице обращаться, какой столбец сортировать. Статический SQL здесь бессилен — нужен динамический.

В PostgreSQL динамический SQL — это возможность PL/pgSQL: команда EXECUTE строит и выполняет SQL-текст в рантайме внутри функции, процедуры или анонимного блока DO $$ ... $$. Из обычного клиента (psql, приложение) вы просто отправляете EXECUTE как часть такого блока — само по себе EXECUTE 'SELECT ...' вне PL/pgSQL не существует.

Когда нужен динамический SQL

• Необязательные фильтры: пользователь может выбрать 0, 1 или 5 условий
• Динамические имена таблиц: архивные таблицы orders_2024, orders_2025
• Генерация отчётов: pivot-запросы с переменным числом колонок
• Универсальные утилиты: функции для аудита, партиционирования

EXECUTE в PL/pgSQL

Базовый синтаксис:

DO $$
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM users';
END;
$$;

Чтобы получить результат, используем INTO:

DO $$
DECLARE
    cnt int;
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM users' INTO cnt;
    RAISE NOTICE 'Пользователей: %', cnt;
END;
$$;

FORMAT: безопасное построение запросов

Конкатенация строк для SQL — прямой путь к SQL-инъекциям. Используйте FORMAT с правильными спецификаторами:

DO $$
DECLARE
    tbl_name  text := 'orders';
    col_name  text := 'status';
    val       text := 'completed';
    sql_query text;
BEGIN
    -- Правильно: %I для имён, %L для значений
    sql_query := FORMAT(
        'SELECT COUNT(*) FROM %I WHERE %I = %L',
        tbl_name, col_name, val
    );
    -- Результат: SELECT COUNT(*) FROM "orders" WHERE "status" = 'completed'
    RAISE NOTICE '%', sql_query;
END;
$$;

Никогда не делайте так: 'SELECT * FROM ' || user_input — пользователь может передать users; DROP TABLE users;-- и вы потеряете данные. %L экранирует одинарные кавычки и не позволяет выйти за пределы строки-значения. %I берёт имя в двойные кавычки, исключая внедрение кода.

Функция с необязательными фильтрами

Реальный кейс: функция поиска заказов, где каждый фильтр необязателен.

CREATE OR REPLACE FUNCTION search_orders(
    p_user_id   int     DEFAULT NULL,
    p_status    text    DEFAULT NULL,
    p_from_date date    DEFAULT NULL,
    p_to_date   date    DEFAULT NULL
)
RETURNS TABLE (
    order_id   int,
    user_id    int,
    status     text,
    total      numeric,
    created_at timestamptz
)
LANGUAGE plpgsql AS $$
DECLARE
    sql_query  text;
    conditions text[] := '{}';  -- массив условий
BEGIN
    sql_query := 'SELECT id, user_id, status, total_amount, created_at FROM orders WHERE 1=1';

    -- Добавляем условия только если параметр передан
    IF p_user_id IS NOT NULL THEN
        conditions := conditions || FORMAT('user_id = %L', p_user_id);
    END IF;

    IF p_status IS NOT NULL THEN
        conditions := conditions || FORMAT('status = %L', p_status);
    END IF;

    IF p_from_date IS NOT NULL THEN
        conditions := conditions || FORMAT('created_at >= %L', p_from_date);
    END IF;

    IF p_to_date IS NOT NULL THEN
        conditions := conditions || FORMAT('created_at < %L', p_to_date + 1);
    END IF;

    -- Склеиваем условия через AND
    IF array_length(conditions, 1) > 0 THEN
        sql_query := sql_query || ' AND ' || array_to_string(conditions, ' AND ');
    END IF;

    sql_query := sql_query || ' ORDER BY created_at DESC LIMIT 1000';

    RETURN QUERY EXECUTE sql_query;
END;
$$;

Использование:

-- Только по статусу
SELECT * FROM search_orders(p_status => 'pending');

-- По пользователю и периоду
SELECT * FROM search_orders(
    p_user_id   => 42,
    p_from_date => '2026-01-01',
    p_to_date   => '2026-03-31'
);

-- Без фильтров — все заказы
SELECT * FROM search_orders();

Клауза USING для безопасных параметров

Для простых случаев вместо %L можно использовать USING — PostgreSQL сам подставит значения безопасно:

DO $$
DECLARE
    p_status text := 'completed';
    cnt      int;
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM orders WHERE status = $1'
    INTO cnt
    USING p_status;

    RAISE NOTICE 'Завершённых заказов: %', cnt;
END;
$$;

USING передаёт параметры как bind variables — SQL-инъекция невозможна по определению. Это предпочтительный способ, когда нужно подставить только значения (не имена таблиц/колонок).

Динамические имена таблиц: пример партиционирования

CREATE OR REPLACE FUNCTION insert_order_partitioned(
    p_order   orders  -- тип "orders" — неявный composite-тип строки таблицы
)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    partition_name text;
BEGIN
    -- Таблица называется orders_YYYYMM
    partition_name := FORMAT('orders_%s',
        TO_CHAR(p_order.created_at, 'YYYYMM'));

    -- Создаём таблицу-партицию если не существует
    EXECUTE FORMAT(
        'CREATE TABLE IF NOT EXISTS %I
         (LIKE orders INCLUDING ALL)',
        partition_name
    );

    -- Вставляем данные
    EXECUTE FORMAT(
        'INSERT INTO %I VALUES ($1.*)',
        partition_name
    ) USING p_order;
END;
$$;

Важный нюанс типизации: %ROWTYPE (как и %TYPE) — это конструкция самого PL/pgSQL, она понимается только внутри тела функции (например, в DECLARE), а не в списке параметров CREATE FUNCTION. Список параметров разбирает основной SQL-парсер, который про %ROWTYPE ничего не знает — p_order orders%ROWTYPE в параметрах даст синтаксическую ошибку. Каждая таблица в PostgreSQL уже имеет неявный одноимённый composite-тип, поэтому для параметра «строка из таблицы orders» достаточно написать просто p_order orders. А вот внутри тела функции %ROWTYPE работает как обычно: DECLARE v_order orders%ROWTYPE;.

Подводные камни

1. Планировщик не может оптимизировать динамический SQL заранее. Каждый вызов EXECUTE — новое планирование. Для частых вызовов это может быть медленнее статических запросов.

2. Ошибки синтаксиса появляются только в рантайме. В отличие от статического SQL, который проверяется при компиляции функции. Покрывайте динамические функции тестами.

3. Логирование затруднено. Добавьте RAISE DEBUG '%', sql_query для отладки в разработке:

SET client_min_messages = DEBUG;

Итог: что использовать в каждом случае

Правило простое: если можно обойтись USING — используйте его. Если нужно подставить имя таблицы/колонки или собрать условия динамически — FORMAT с %I/%L. Конкатенация через || пользовательского ввода — не вариант никогда.

Потренируйтесь: найдите уязвимость

В функции ниже спрятана SQL-инъекция:

CREATE OR REPLACE FUNCTION search_products(p_name text)
RETURNS SETOF products LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM products WHERE name ILIKE ''%' || p_name || '%''';
END;
$$;

Что произойдёт при вызове search_products('x'' OR ''1''=''1'' --')? Как исправить функцию?

SELECT * FROM products WHERE name ILIKE '%x' OR '1'='1' --%'

CREATE OR REPLACE FUNCTION search_products(p_name text)
RETURNS SETOF products LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM products WHERE name ILIKE ''%'' || $1 || ''%'''
    USING p_name;
END;
$$;

Вопросы для самопроверки

1. Почему %I и %L — не два взаимозаменяемых способа экранирования, а разные вещи? Что случится, если перепутать их местами?
2. В функции search_orders условия сначала собираются в массив conditions, а не сразу склеиваются в строку WHERE. Зачем?
3. Чем EXECUTE '...' USING v отличается от EXECUTE FORMAT('...%L...', v) — и можно ли всегда заменять второе первым?
4. Почему статический SQL внутри функции PL/pgSQL планируется один раз за сессию и переиспользуется при повторных вызовах, а динамический — заново при каждом вызове? Как это влияет на производительность?
5. Приведите свой пример задачи, где без динамического SQL не обойтись — то есть где не помогут ни статический запрос, ни USING с фиксированным текстом.

Хотите освоить PL/pgSQL и продвинутый PostgreSQL на практике? Попробуйте тренажёр SQLlab.