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

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

Когда нужен динамический 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%ROWTYPE
)
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;
$$;

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

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

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

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

SET client_min_messages = DEBUG;

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