Menu

CTE в PostgreSQL: запросы WITH и WITH RECURSIVE

Обобщённое табличное выражение (CTE) даёт подзапросу имя в предложении WITH, и основной запрос читает его как таблицу. WITH RECURSIVE позволяет ссылаться на себя и обходить деревья и графы.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Обобщённое табличное выражение (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 существует только в той команде, в которой он записан. Чтобы переиспользовать запрос в разных командах, создайте представление или временную таблицу.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать