The SQL FORMAT Function: String Templates with %s, %I and %L in PostgreSQL

format() в PostgreSQL собирает строку по шаблону, как printf в Си: вы пишете шаблон со спецификаторами, а функция подставляет на их места аргументы. Берите её, когда нужно склеить приветствие, путь к таблице или целый текст запроса для динамического SQL — то, что через оператор || быстро превращается в нечитаемую кашу из кавычек.

Главное преимущество format() перед конкатенацией — спецификаторы %I и %L, которые сами закавычивают идентификаторы и экранируют литералы. Благодаря им функция отделяет шаблон от данных и даёт базе правильно обработать опасные части, поэтому именно format() в PostgreSQL — штатный способ безопасно собирать динамический SQL внутри EXECUTE и функций на PL/pgSQL.

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

format(template, args...) берёт строку-шаблон и подставляет аргументы вместо спецификаторов. Самый простой из них — %s, который вставляет значение как текст:

SELECT format('Hi %s, id=%s', name, id) AS greeting
FROM users
WHERE country = 'US';

Что важно знать про %s:

• Любой аргумент приводится к тексту через свой ::text, поэтому числа, даты и булевы значения работают сразу.
• NULL превращается в пустую строку, а не в слово NULL — это частый сюрприз.
• Чтобы вставить буквальный знак процента, удваивайте его: %%.

-- NULL becomes an empty string, not the text 'NULL'
SELECT format('name=[%s]', NULL) AS demo;   -- name=[]
SELECT format('100%% done') AS pct;          -- 100% done

%I и %L: безопасный динамический SQL

Главная причина любить format() — это спецификаторы %I и %L. %I оформляет аргумент как идентификатор (имя таблицы или колонки), %L — как строковый литерал, со всем экранированием кавычек.

-- %I quotes an identifier, %L quotes a literal
SELECT format('SELECT * FROM %I WHERE email = %L', 'users', 'a@b.com');
-- SELECT * FROM users WHERE email = 'a@b.com'

Это и есть защита от SQL-инъекций в динамических запросах. Сравните с наивной конкатенацией внутри функции:

CREATE FUNCTION count_by_status(tbl text, st text)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  n bigint;
BEGIN
  -- Safe: %I and %L handle quoting and escaping for us
  EXECUTE format('SELECT count(*) FROM %I WHERE status = %L', tbl, st)
  INTO n;
  RETURN n;
END;
$$;

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

Если бы здесь стояло '... WHERE status = ''' || st || '''', то значение st вида x'' OR ''1''=''1 сломало бы запрос. %L экранирует апострофы автоматически, а %I корректно закавычит имя weird table или зарезервированное слово вроде order.

Ловушка: для имени схемы с таблицей не пишите %I на всю строку public.orders — иначе получится один идентификатор "public.orders". Передавайте части по отдельности: format('%I.%I', 'public', 'orders').

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

Когда один аргумент нужен несколько раз, удобны позиционные ссылки вида %n$. Цифра — это номер аргумента, начиная с единицы:

-- %1$ refers to the first argument, reused twice
SELECT format('%1$s <%2$s> aka %1$s', name, email)
FROM users
LIMIT 3;

Так шаблон становится короче, а перечислять аргумент дважды не нужно. Позиционную форму можно смешивать с %I и %L:

SELECT format(
  'INSERT INTO %1$I (email) VALUES (%2$L) -- into %1$I',
  'users', 'new@b.com'
);

FORMAT против конкатенации

Тот же результат можно собрать оператором ||, но цена — читаемость и безопасность. Сравните две версии письма-уведомления:

-- Concatenation: hard to read, easy to misplace a quote
SELECT 'Order ' || o.id || ' for ' || u.name
       || ': ' || o.amount || ' (' || o.status || ')'
FROM orders o JOIN users u ON u.id = o.user_id;

-- format(): the template reads like the output
SELECT format('Order %s for %s: %s (%s)', o.id, u.name, o.amount, o.status)
FROM orders o JOIN users u ON u.id = o.user_id;

Почему format() обычно выигрывает:

• Шаблон видно целиком, без визуального шума из кавычек и ||.
• NULL не отравляет всю строку: при конкатенации 'a' || NULL даёт NULL, а в format() это просто пустая подстановка.
• Для динамического SQL %I/%L дают защиту, которой у || нет в принципе.

MySQL и ClickHouse

Важный нюанс переносимости: в MySQL есть функция с тем же именем FORMAT, но она про другое — форматирует число с разделителями разрядов, а не собирает строку по шаблону:

-- MySQL: FORMAT formats a NUMBER, not a template
SELECT FORMAT(1234567.891, 2);   -- 1,234,567.89

Аналог шаблонной сборки в MySQL — это CONCAT, CONCAT_WS (с разделителем) и printf-подобная MAKE_SET/ELT для частных случаев. Прямого эквивалента %I/%L нет; для безопасного динамического SQL используйте подготовленные выражения с плейсхолдерами ?. ClickHouse предлагает format() с фигурными скобками {0}, {1} как у Python:

-- ClickHouse: positional braces, not percent specifiers
SELECT format('Hi {0}, id={1}', name, toString(id)) FROM users;

Эти различия легко проглядеть при переносе кода: одно и то же имя FORMAT в MySQL ждёт число и точность, в PostgreSQL — шаблон со спецификаторами %s, а ClickHouse использует фигурные скобки {0}. Поэтому шаблонную сборку строк нельзя копировать между движками вслепую: проверяйте, какую из трёх несовместимых функций вы на самом деле вызываете, и помните, что аналога %I/%L за пределами PostgreSQL нет — там безопасность динамического SQL обеспечивают подготовленные выражения с плейсхолдерами ?.

Отдельно стоит проверить поведение format() на краевых данных, ведь именно %s молча превращает NULL в пустую строку, а %L обязан корректно экранировать апострофы и Unicode внутри литералов. Прогоните шаблон на значениях с NULL, пустой строкой, кавычкой и зарезервированным именем таблицы: так вы убедитесь, что %I и %L действительно закрывают инъекцию там, где наивная конкатенация || ломается.

Вывод: в PostgreSQL format() — это и удобный printf для отчётов, и единственно правильный способ строить динамический SQL благодаря %I и %L. В MySQL и ClickHouse одноимённые функции делают совсем другое, проверяйте документацию.