format() в PostgreSQL: как собирать строки и безопасный динамический SQL

format() в PostgreSQL — это функция для сборки строки по шаблону. Она похожа на printf из других языков: вы пишете текст с местами для подстановки, а PostgreSQL вставляет туда значения.

Например:

SELECT format('Hi %s, id=%s', 'Ann', 42) AS message;

Результат:

Hi Ann, id=42

На первый взгляд кажется: «Ну и что? Строки ведь можно склеивать через оператор ||». Можно. Но как только строка становится чуть сложнее, обычная конкатенация быстро превращается в кашу из кавычек, пробелов и вертикальных палок.

Особенно полезна format() в двух случаях:

1. Когда нужно собрать красивый текст: сообщение, подпись, путь, строку отчёта.
2. Когда нужно собрать динамический SQL внутри функции PostgreSQL.

Во втором случае format() особенно важна, потому что у неё есть специальные подстановки для безопасной работы с именами таблиц, колонок и строковыми значениями: %I и %L.

Зачем вообще нужна format()

Допустим, мы хотим собрать строку с описанием заказа:

Order 1001 for Ann: 2500 paid

Через конкатенацию это будет выглядеть так:

SELECT
    'Order ' || o.id || ' for ' || u.name || ': ' || o.amount || ' ' || o.status AS message
FROM orders o
JOIN users u ON u.id = o.user_id;

Работает, но читается тяжеловато. Глазам приходится прыгать между кусками текста, кавычками и оператором ||.

С format() шаблон видно целиком:

SELECT
    format('Order %s for %s: %s %s', o.id, u.name, o.amount, o.status) AS message
FROM orders o
JOIN users u ON u.id = o.user_id;

Теперь строка читается почти как будущий результат:

Order 1001 for Ann: 2500 paid

Главная идея format() простая:

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

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

Общий вид функции:

format(template, value1, value2, value3, ...)

Первый аргумент — строка-шаблон.

Внутри шаблона стоят спецификаторы. Самый простой спецификатор — %s.

SELECT format('User: %s, country: %s', 'Ann', 'DE') AS result;

Результат:

User: Ann, country: DE

Первый %s заменился на 'Ann', второй %s — на 'DE'.

Значения подставляются по порядку: первый спецификатор берёт первый аргумент после шаблона, второй спецификатор — второй аргумент, и так далее.

%s: обычная текстовая подстановка

Спецификатор %s вставляет значение как текст.

SELECT format('id=%s, active=%s', 42, true) AS result;

Результат:

id=42, active=true

PostgreSQL сам приводит значения к текстовому виду. Поэтому можно подставлять числа, даты, булевы значения и другие типы.

Например:

SELECT format(
    'created=%s, amount=%s',
    DATE '2024-03-15',
    1499.50
) AS result;

Результат:

created=2024-03-15, amount=1499.50

Это удобно для сообщений, логов и отчётов.

Что %s делает с NULL

У %s есть важная особенность: если значение равно NULL, оно превращается в пустую строку.

SELECT format('name=[%s]', NULL) AS result;

Результат:

name=[]

Это отличается от обычной конкатенации.

SELECT 'name=[' || NULL || ']' AS result;

Результат будет NULL, потому что в PostgreSQL строка плюс NULL даёт NULL.

С format() вся строка не ломается. Просто место подстановки остаётся пустым.

Иногда это удобно, а иногда может скрыть проблему. Например, если имя пользователя обязательно должно быть заполнено, пустая подстановка может выглядеть как «тихая потеря данных». Поэтому для важных полей лучше заранее решить, как показывать NULL.

Например, можно использовать COALESCE.

SELECT format('name=[%s]', COALESCE(name, 'unknown')) AS result
FROM users;

Как вывести знак процента

Если внутри шаблона нужен обычный знак %, его нужно удвоить.

SELECT format('Progress: 100%%') AS result;

Результат:

Progress: 100%

Один % PostgreSQL воспринимает как начало спецификатора. Поэтому для обычного процента пишем %%.

Почему format() лучше конкатенации

Сравним два варианта.

Конкатенация:

SELECT
    'User ' || u.id || ': ' || u.email || ' from ' || u.country AS line
FROM users u;

format():

SELECT
    format('User %s: %s from %s', u.id, u.email, u.country) AS line
FROM users u;

Во втором варианте проще увидеть будущую строку. Шаблон не разорван на куски.

Но дело не только в красоте. У format() есть ещё одно огромное преимущество: специальные спецификаторы для динамического SQL.

%I: безопасно вставить имя таблицы или колонки

Спецификатор %I используется для идентификаторов.

Идентификаторы — это имена таблиц, колонок, схем и других объектов базы.

Например:

SELECT format('SELECT %I FROM %I', 'email', 'users') AS sql_text;

Результат:

SELECT email FROM users

Если имя обычное, PostgreSQL оставит его без кавычек.

Но если имя странное, содержит пробел или совпадает с ключевым словом, %I аккуратно обернёт его в двойные кавычки.

SELECT format('SELECT * FROM %I', 'order') AS sql_text;

Результат:

SELECT * FROM "order"

Это важно, потому что order — слово, которое в SQL используется в конструкции ORDER BY. Без кавычек такое имя может сломать запрос.

Ещё пример:

SELECT format('SELECT * FROM %I', 'user table') AS sql_text;

Результат:

SELECT * FROM "user table"

%I делает то, что обычно приходится делать руками через кавычки, но делает это правильно.

%L: безопасно вставить SQL-литерал

Спецификатор %L используется для SQL-литералов.

Литерал — это значение внутри SQL-запроса: строка, число, дата, NULL.

Например:

SELECT format('SELECT * FROM users WHERE email = %L', 'ann@example.com') AS sql_text;

Результат:

SELECT * FROM users WHERE email = 'ann@example.com'

PostgreSQL сам поставил одинарные кавычки вокруг email.

Если внутри значения есть апостроф, %L правильно его экранирует.

SELECT format('SELECT %L AS author', 'O''Reilly') AS sql_text;

Результат:

SELECT 'O''Reilly' AS author

Это особенно важно для безопасности. Если вставлять значения в SQL через обычную конкатенацию, можно случайно открыть дверь для SQL-инъекции.

%I и %L на одном примере

Допустим, нужно собрать запрос:

SELECT count(*) FROM orders WHERE status = 'paid'

Имя таблицы и статус приходят как параметры.

Через format():

SELECT format(
    'SELECT count(*) FROM %I WHERE status = %L',
    'orders',
    'paid'
) AS sql_text;

Результат:

SELECT count(*) FROM orders WHERE status = 'paid'

Здесь:

• %I безопасно подставил имя таблицы;
• %L безопасно подставил строковое значение.

Это очень важное различие. Имя таблицы и строковое значение — разные сущности. Их нельзя экранировать одинаково.

Для таблиц и колонок используйте %I.

Для значений используйте %L.

Для обычного текста используйте %s.

Динамический SQL в PL/pgSQL

format() часто используют внутри функций на PL/pgSQL, когда нужно выполнить запрос, который заранее неизвестен полностью.

Например, хотим написать функцию, которая считает строки с нужным статусом в переданной таблице.

CREATE FUNCTION count_by_status(tbl text, st text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    EXECUTE format(
        'SELECT count(*) FROM %I WHERE status = %L',
        tbl,
        st
    )
    INTO n;

    RETURN n;
END;
$$;

Теперь можно вызвать:

SELECT count_by_status('orders', 'paid');

Функция соберёт и выполнит запрос:

SELECT count(*) FROM orders WHERE status = 'paid'

Главное здесь — не сам подсчёт, а безопасная сборка запроса. Таблица вставляется через %I, значение статуса — через %L.

Ещё лучше: EXECUTE ... USING для значений

В PL/pgSQL есть ещё более хороший приём: имена таблиц и колонок собирать через %I, а значения передавать через USING.

CREATE FUNCTION count_by_status(tbl text, st text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    EXECUTE format(
        'SELECT count(*) FROM %I WHERE status = $1',
        tbl
    )
    USING st
    INTO n;

    RETURN n;
END;
$$;

Здесь имя таблицы всё равно приходится вставлять в текст запроса, потому что параметры не работают на месте имён таблиц и колонок.

А вот значение st передаётся отдельно через USING. Это чистый и безопасный способ: PostgreSQL сам работает со значением как с параметром, а не как с куском строки.

Практическое правило:

• для имён таблиц, схем и колонок используйте %I;
• для значений в динамическом SQL по возможности используйте USING;
• если нужно именно вставить значение в текст запроса, используйте %L.

Почему нельзя просто склеивать динамический SQL

Наивный вариант может выглядеть так:

CREATE FUNCTION bad_count_by_status(tbl text, st text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    EXECUTE 'SELECT count(*) FROM ' || tbl || ' WHERE status = ''' || st || ''''
    INTO n;

    RETURN n;
END;
$$;

Такой код неприятен сразу по нескольким причинам.

Во-первых, его трудно читать. Нужно внимательно считать кавычки.

Во-вторых, его легко сломать значением с апострофом.

В-третьих, это опасно, если входные данные не полностью контролируются.

С format() запрос выглядит спокойнее:

EXECUTE format(
    'SELECT count(*) FROM %I WHERE status = %L',
    tbl,
    st
);

Шаблон читается отдельно, данные передаются отдельно, PostgreSQL сам правильно оформляет опасные части.

Важная ловушка: схема и таблица

Если у вас есть схема и таблица, не передавайте их вместе в один %I.

Плохо:

SELECT format('SELECT * FROM %I', 'public.orders') AS sql_text;

Результат:

SELECT * FROM "public.orders"

PostgreSQL воспримет это как один идентификатор с точкой внутри имени. Это не то же самое, что схема public и таблица orders.

Правильно передавать части отдельно:

SELECT format('SELECT * FROM %I.%I', 'public', 'orders') AS sql_text;

Результат:

SELECT * FROM public.orders

То же правило работает для любых составных имён:

SELECT format('%I.%I.%I', 'db', 'schema', 'table_name') AS full_name;

Каждая часть имени должна экранироваться отдельно.

%L и NULL

Спецификатор %L умеет правильно работать с NULL.

SELECT format('SELECT * FROM users WHERE deleted_at IS %L', NULL) AS sql_text;

Результат:

SELECT * FROM users WHERE deleted_at IS NULL

Но с NULL в условиях нужно быть внимательным.

Например, такой запрос будет синтаксически корректным:

SELECT format('SELECT * FROM users WHERE email = %L', NULL) AS sql_text;

Результат:

SELECT * FROM users WHERE email = NULL

Но логически это почти всегда ошибка. В SQL нельзя проверять NULL через =. Для этого нужен IS NULL.

То есть %L правильно оформит значение, но не придумает за вас правильную бизнес-логику.

Если значение может быть NULL, условие иногда нужно собирать отдельно:

SELECT
    CASE
        WHEN email_value IS NULL THEN 'email IS NULL'
        ELSE format('email = %L', email_value)
    END AS condition_sql
FROM params;

%I и NULL

Для %I значение NULL недопустимо.

SELECT format('SELECT * FROM %I', NULL);

Имя таблицы или колонки не может быть неизвестным. Поэтому такой запрос завершится ошибкой.

Это полезная защита: если вы случайно не передали имя таблицы, PostgreSQL не соберёт странный запрос, а сразу остановится.

Позиционные спецификаторы

Иногда один и тот же аргумент нужен в шаблоне несколько раз.

Можно, конечно, передать его дважды:

SELECT format('%s <%s> signed as %s', 'Ann', 'ann@example.com', 'Ann') AS result;

Но есть удобнее: позиционные спецификаторы.

SELECT format('%1$s <%2$s> signed as %1$s', 'Ann', 'ann@example.com') AS result;

Результат:

Ann <ann@example.com> signed as Ann

%1$s означает: взять первый аргумент после шаблона.

%2$s означает: взять второй аргумент после шаблона.

Позиции считаются с единицы.

Позиционные спецификаторы можно сочетать с %I и %L.

SELECT format(
    'INSERT INTO %1$I (email) VALUES (%2$L) RETURNING * FROM %1$I',
    'users',
    'new@example.com'
) AS sql_text;

Результат:

INSERT INTO users (email) VALUES ('new@example.com') RETURNING * FROM users

Здесь %1$I дважды использует первый аргумент как идентификатор.

Ширина и выравнивание

У format() есть не только простые подстановки. Можно задавать минимальную ширину поля.

SELECT format('|%10s|', 'cat') AS result;

Результат:

|       cat|

Значение выровнено вправо внутри поля шириной 10 символов.

Для выравнивания влево используется минус:

SELECT format('|%-10s|', 'cat') AS result;

Результат:

|cat       |

Это полезно для текстовых отчётов, когда хочется сделать ровные колонки.

Например:

SELECT format('|%-10s|%8s|', 'status', 'count') AS header;

Результат:

|status    |   count|

В обычном API-коде это нужно редко, но для логов, отладочных сообщений и текстовых выгрузок бывает удобно.

Когда брать format(), а когда concat

В PostgreSQL есть ещё функции concat и concat_ws.

concat просто склеивает значения:

SELECT concat('User ', id, ': ', email) AS result
FROM users;

concat_ws склеивает значения через разделитель:

SELECT concat_ws(', ', city, country) AS location
FROM users;

Если нужно просто склеить 2–3 значения, concat вполне нормален.

Если нужен читаемый шаблон с несколькими подстановками, удобнее format().

Сравните:

SELECT concat('User ', id, ' from ', country, ': ', email) AS result
FROM users;

и:

SELECT format('User %s from %s: %s', id, country, email) AS result
FROM users;

Во втором варианте будущий текст виден сразу.

А если вы собираете динамический SQL, особенно с именами таблиц и колонок, format() с %I и %L — почти всегда лучший выбор.

Как выбрать спецификатор

Для обычного текста используйте %s.

SELECT format('Hi %s', 'Ann') AS result;

Для имени таблицы, колонки или схемы используйте %I.

SELECT format('SELECT %I FROM %I', 'email', 'users') AS sql_text;

Для SQL-значения внутри текста запроса используйте %L.

SELECT format('WHERE email = %L', 'ann@example.com') AS condition_sql;

Для обычного процента используйте %%.

SELECT format('Success: 100%%') AS result;

Это главный набор, который нужен в повседневной работе.

Практический пример: создать запрос по колонке

Допустим, у нас есть функция, которая считает количество строк, где указанная колонка равна указанному значению.

CREATE FUNCTION count_by_column(tbl text, col text, val text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    EXECUTE format(
        'SELECT count(*) FROM %I WHERE %I = $1',
        tbl,
        col
    )
    USING val
    INTO n;

    RETURN n;
END;
$$;

Вызов:

SELECT count_by_column('users', 'country', 'DE');

Внутри получится запрос по смыслу:

SELECT count(*) FROM users WHERE country = 'DE'

Здесь таблица и колонка вставлены через %I, потому что это идентификаторы.

Значение передано через USING, потому что это данные.

Такой стиль хорошо показывает границу между структурой запроса и данными.

Практический пример: динамическая очистка таблицы

Допустим, нужно удалить старые строки из таблицы, имя которой передаётся параметром.

CREATE FUNCTION delete_old_rows(tbl text, cutoff timestamp)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    EXECUTE format(
        'DELETE FROM %I WHERE created_at < $1',
        tbl
    )
    USING cutoff;

    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;

Вызов:

SELECT delete_old_rows('events', TIMESTAMP '2024-01-01 00:00:00');

Здесь %I защищает имя таблицы, а дата передаётся как параметр через USING.

Это намного лучше, чем склеивать дату в строку вручную.

MySQL и ClickHouse: осторожно с переносом

Название format встречается не только в PostgreSQL, но в разных СУБД оно может означать разные вещи.

В MySQL FORMAT — это не сборка строки по шаблону. Там эта функция форматирует число.

SELECT FORMAT(1234567.891, 2);

Результат:

1,234,567.89

То есть MySQL FORMAT ждёт число и количество знаков после запятой, а не строку с %s.

Для обычной сборки строк в MySQL чаще используют CONCAT и CONCAT_WS.

SELECT CONCAT('Hi ', name, ', id=', id) AS message
FROM users;

Для безопасного динамического SQL в MySQL нужно использовать подготовленные выражения и параметры, а не пытаться перенести PostgreSQL-спецификаторы %I и %L.

В ClickHouse тоже есть функция format, но синтаксис другой: там используются фигурные скобки с позициями.

SELECT format('Hi {0}, id={1}', name, toString(id))
FROM users;

В PostgreSQL используются %s, %I, %L.

В ClickHouse — {0}, {1}.

В MySQL FORMAT вообще про форматирование чисел.

Поэтому при переносе SQL между базами не стоит копировать format() вслепую. Одинаковое имя функции ещё не значит, что поведение одинаковое.

Частые ошибки

Первая ошибка — использовать %s для имени таблицы.

SELECT format('SELECT * FROM %s', 'users');

Для простого имени это может выглядеть нормально, но безопаснее и правильнее использовать %I.

SELECT format('SELECT * FROM %I', 'users');

Вторая ошибка — использовать %s для строкового значения в SQL-запросе.

SELECT format('SELECT * FROM users WHERE email = %s', 'ann@example.com');

Получится некорректный SQL, потому что email не будет заключён в кавычки.

Правильно:

SELECT format('SELECT * FROM users WHERE email = %L', 'ann@example.com');

Третья ошибка — передавать public.orders в один %I.

SELECT format('SELECT * FROM %I', 'public.orders');

Правильно:

SELECT format('SELECT * FROM %I.%I', 'public', 'orders');

Четвёртая ошибка — забыть, что %s превращает NULL в пустую строку.

SELECT format('name=[%s]', NULL);

Результат:

name=[]

Если нужно явно показать пропуск, используйте COALESCE.

SELECT format('name=[%s]', COALESCE(name, 'unknown'))
FROM users;

Пятая ошибка — думать, что %L сам исправит логику сравнения с NULL.

SELECT format('email = %L', NULL);

Получится:

email = NULL

Но для проверки на NULL в SQL нужно IS NULL, а не =.

Главное из статьи

format() в PostgreSQL собирает строку по шаблону.

%s подставляет значение как текст.

%% выводит обычный знак процента.

%I безопасно оформляет идентификатор: имя таблицы, колонки или схемы.

%L безопасно оформляет SQL-литерал: строку, дату, число или NULL.

Для динамического SQL в PL/pgSQL обычно используют format() вместе с EXECUTE.

Имена таблиц и колонок передавайте через %I.

Значения в динамическом SQL по возможности передавайте через USING; если значение нужно именно вставить в текст запроса, используйте %L.

Не передавайте public.orders в один %I; схему и таблицу нужно экранировать отдельно через %I.%I.

Для простых сообщений format() делает код читабельнее, чем длинная конкатенация через ||.

Главное правило: если собираете обычный текст — format() помогает писать чище. Если собираете динамический SQL — format() с %I и %L помогает писать безопаснее.