Обобщённое табличное выражение (CTE) это именованный подзапрос в предложении WITH перед основной командой: WITH name AS (SELECT ...) SELECT ... FROM name. Основной запрос читает CTE как таблицу, и вложенные подзапросы превращаются в шаги, которые читаются сверху вниз.
CTE totals суммирует заказы каждого клиента, а основной запрос оставляет клиентов с суммой больше 100: cy с 300 и ana с 180. CTE существует только в той команде, к которой относится.
Цепочка из нескольких CTE
Разделяйте CTE запятыми. Каждый из них может читать CTE, объявленные до него.
average читает totals, а основной запрос читает оба. Суммы клиентов равны 180, 70 и 300, их среднее около 183, поэтому выше него только cy.
Обход дерева с WITH RECURSIVE
WITH RECURSIVE позволяет CTE ссылаться на самого себя. Он состоит из двух частей, соединённых UNION ALL:
- начальный запрос (якорь), который возвращает стартовые строки;
- рекурсивный запрос, который соединяет CTE с таблицей, чтобы найти следующий уровень.
PostgreSQL выполняет рекурсивную часть на строках, полученных на предыдущем шаге, и останавливается, когда шаг не вернул ни одной строки.
Каждый шаг находит сотрудников, которые подчиняются строкам предыдущего шага. Mia оказывается на глубине 3 с путём Rosa > Omar > Ivo > Mia. Чтобы вывести всех подчинённых одного руководителя, а не всё дерево, замените WHERE якоря на id = 2.
Чтобы идти вверх, от сотрудника к вершине, поменяйте условие соединения на s.id = c.manager_id.
Остановить бесконечный рекурсивный CTE
Рекурсивный CTE заканчивается, только когда шаг ничего не вернул. На данных с циклом (A подчиняется B, B подчиняется A) он не закончится никогда. Добавьте ограничение глубины, например WHERE c.depth < 20, или используйте предложение CYCLE (PostgreSQL 14+), которое помечает первую повторившуюся строку и дальше по ней не идёт:
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
Генерация строк рекурсивным CTE
Рекурсивной части не нужна таблица. Этот запрос считает от 1 до 5, передавая значение каждой строки на следующий шаг:
Для простых последовательностей чисел или дат короче generate_series.
Перенос строк через CTE с изменением данных
CTE может быть INSERT, UPDATE или DELETE с предложением RETURNING, и основная команда читает возвращённые строки. Так старые заказы переносятся в архивную таблицу одной командой:
Заказы 1 и 3 удаляются и вставляются в архив вместе, поэтому при сбое обе таблицы остаются такими, какими были. Все части команды видят один и тот же снимок данных: основной запрос не видит действия DELETE на orders, только то, что передаёт RETURNING (см. RETURNING).
Управление встраиванием через MATERIALIZED
Начиная с PostgreSQL 12 CTE без побочных эффектов, на который основной запрос ссылается один раз, встраивается. Планировщик оптимизирует его вместе с внешним запросом, так что WHERE, написанный снаружи CTE, всё равно может использовать индекс. CTE, на который ссылаются два раза и больше, вычисляется один раз, а результат переиспользуется. Любое из этих поведений можно задать явно:
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
MATERIALIZED вычисляет CTE один раз, отдельно, и не пускает в него внешний WHERE. Используйте его, когда встроенный план оказывается медленнее. NOT MATERIALIZED просит встроить CTE, даже если на него ссылаются больше одного раза. Рекурсивные CTE и CTE с изменением данных всегда вычисляются отдельно.
Часто задаваемые вопросы
CTE в PostgreSQL быстрее подзапроса?
Обычно ни один не быстрее. Начиная с PostgreSQL 12 CTE, на который ссылаются один раз и у которого нет побочных эффектов, встраивается в основной запрос и получает тот же план, что и равноценный подзапрос. До версии 12 каждый CTE вычислялся отдельно, и это могло мешать использованию индексов.
Можно ли использовать CTE в UPDATE или DELETE?
Да. WITH можно ставить перед INSERT, UPDATE, DELETE и MERGE, а сам CTE может быть INSERT, UPDATE или DELETE с RETURNING, чьи строки читает основная команда.
Как остановить бесконечный цикл в рекурсивном CTE?
Дайте рекурсивной части условие, которое в какой-то момент перестанет возвращать строки, например ограничение глубины (WHERE depth < 10). Для данных, которые могут замыкаться сами на себя, предложение CYCLE (PostgreSQL 14+) помечает повторившуюся строку и останавливается на ней.
Можно ли использовать CTE в нескольких запросах?
Нет. CTE существует только в той команде, в которой он записан. Чтобы переиспользовать запрос в разных командах, создайте представление или временную таблицу.