PostgreSQL. Рекурсивные CTE (draft).

В заметке приводятся примеры использования рекурсивных CTE в PostgreSQL.

Рассмотрим элементарный пример рекурсивного CTE, возвращающего последовательность целых чисел от 1 до 10.

with recursive nums(iteration) as
( select 1 -- Якорный элемент

  union all
  
  select iteration + 1 --Рекурсивный элемент
  from nums
  where iteration < 10
)
select * from nums
order by iteration

Этот рекурсивный CTE состоит из следующих элементов:

Якорный элемент - в примере это запрос select 1. В общем случае это запрос, который возвращает корректный результат и вызывается всего один раз.

Рекурсивный элемент - в примере это запрос, который идёт после union all. Этот запрос содержит ссылку на имя всего CTE (результирующий набор, полученный на предыдущей итерации) - в примере nums. При первом вызове рекурсивного элемента предшествующий результат представлен значениями, которые вернул закрепленный элемент. При каждом следующем вызове имя CTE представляет результат предыдущего выполнения рекурсивного элемента.

Т.к. между запросами якорного и рекурсивного элемента находится кляуза union all (или union, если по каким-то причинам нужно убрать дубли), очевидно, что оба запроса должны быть совместимы по количеству и типу возвращаемых
столбцов.

Этот пример - генерация числовой последовательности - одно из возможных применений рекурсивного CTE. Но чаще они используются для работы с иерархическими данными.

Итак, поработаем с иерархическими данными на примере известной схемы SCOTT. Выведем номер, имя и должность всех начальников из таблицы scott.emp для выбранного работника на всех уровнях иерархии. Будем искать для empno = 7369. Скрипты создания таблицы и её наполнения можно взять на моём github

with recursive mgr_list (e_level, empno, ename, job, mgr) as (
select 1, a.empno, a.ename, a.job
, a.mgr
from scott.emp a
where a.empno = 7369

union all

select e_level + 1, a.empno, a.ename, a.job
, a.mgr
FROM mgr_list r 
join scott.emp a on r.mgr = a.empno
)

select m.*
, a.ename as mgr_name, a.job as mgr_job 
from mgr_list m
join scott.emp a on m.mgr = a.empno

1. В якорной части конструкции WITH этого запроса выбирается только 1 строка из таблицы для работника с номером 7369 (на скрине выше строка с e_level = 1). Эта строка помещается во временную рабочую таблицу и в результирующую таблицу.

2. Далее в рекурсивной части мы соединяем эту временную рабочую таблицу под алиасом mgr_list с scott.emp. Условие соединения r.mgr = a.empno джойнит строки, в которых позиция работника на следующем уровне иерархии из таблицы scott.emp совпадает с позицией начальника работника на текущем уровне иерархии из датасета mgr_list. На выходе строка с e_level равным 2 на скрине выше доставляется в результирующую таблицу (там уже 2 записи) и во временную таблицу - там только 1 запись.

3. На этом шаге содержимое временной рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица очищается. После чего аналогично пункту 2 получаем строку с e_level равным 3 на скрине выше. Она доставляется в результирующую таблицу (там уже 3 записи) и в недавно очищенную временную таблицу - там только 1 запись.

4. Повторяем пункты 2 и 3 для работника

Теперь после очистки рабочей таблицы и заполнением её строкой из промежуточной таблицы с данными работника с должностью президент и значением NULL в поле mgr наши итерации заканчиваются (джойн по условию r.mgr = a.empno возвращает пустоту). И все накопленные в результирующей таблице строки доступны внешнему запросу.

Более подробно по теме можно почитать в зачётной книжке PostgreSQL. Профессиональный SQL

Оттуда я взял картинки для иллюстрации пунктов 1 и 3. А ещё там много поучительных задачек, которые можно прорешать и потом преисполниться.

P.S. Кстати, убедиться в том, что в каждую итерацию в рабочую таблицу приходит ровно 1 строка, можно посмотрев план запроса.

FIXME пп 3-4 откорректировать