Одной из продвинутых возможностей Postgres является возможность написания рекурсивных запросов. Не то чтобы она была сильно продвинутой, но новички о ней часто не слышали, как и о CTE (Common Table Expressions), на основе которых и работает рекурсивность в постгре.

Используя ключевое слово WITH, мы можем создать временную таблицу (это как раз и есть CTE, она существует только на время выполнения запроса), и в сочетании с RECURSIVE мы можем делать довольно прикольные штуки, вроде обхода дерева, задачи коммивояжёра или просто взять и распечатать рекурсивно числа, как в примере:

with recursive numbers as (
    select 1 as n
    union all
    select n + 1
    from numbers
    where n < 5
)
select n
from numbers;

Этот запрос просто выведет числа от 1 до 5. Давайте построчно разберём этот примерчик:

• with recursive numbers as (

Создаём временную таблицу и говорим, что дальше будет рекурсивный запрос

• select 1 as n

Объявляем первоначальное значение, на основе которого заполнится наша временная таблица numbers

• union all

Все результаты объединятся

• select n + 1 from numbers where n < 5

Это уже рекурсивная часть, мы конструируем новые значения, заполняя numbers на основе само́й себя 🙃. Звучит не особо понятно, но так и есть. Можно перефразировать, мы выбираем новое значение на основе предыдущего, вставляем его в таблицу, выбираем его, и так пока не будет нарушено условие завершения n < 5. И да, нужно быть повнимательнее с условием завершения, иначе можно попасть в бесконечный цикл.

• select n from numbers

Это уже просто. Мы просто выбираем всё n из вре́менной таблицы numbers.

Супер. Теперь вы уже умеете писать рекурсивные запросы, можно прощаться. Шучу, слишком коротко, давайте теперь я покажу два примера, для которых эту конструкцию часто используют.

Для этих примеров я буду использовать простую таблицу сотрудников:

create table employees
(
    id      serial primary key,
    name    varchar,
    manager int references employees
)

Таблица состоит из автоинкрементного id, имени сотрудника и поля с id менеджера (ссылающийся на саму таблицу). Визуально оно выглядит так:

И сразу наполним данными:

insert into employees (id, name, manager)
values (1, 'Kevin Wilkerson', null),
       (2, 'Darin Montgomery', 1),
       (3, 'Joyce Campbell', 1),
       (4, 'Alfred Brewer', 2),
       (5, 'Carroll Jimenez', 3),
       (6, 'Jamie Sims', 3),
       (7, 'Nettie Lawrence', 5);

Поиск предков конкретного узла

Формулируем задачу. Найти всех начальников конкретного сотрудника. К примеру, для Nettie Lawrence:

with recursive managers as (
    select id, name, manager
    from employees
    where name = 'Nettie Lawrence'

    union all

    select e.id, e.name, e.manager
    from employees as e
             inner join managers as m on e.id = m.manager
)
select id, name, manager
from managers;

----+-----------------+---------
 id |      name       | manager
----+-----------------+---------
  7 | Nettie Lawrence |       5
  5 | Carroll Jimenez |       3
  3 | Joyce Campbell  |       1
  1 | Kevin Wilkerson |
(4 rows)

Если разбирать каждую часть запроса, должно быть понятно, как он работает. Мы выбираем строку с сотрудником, начальников которого нам нужно найти. Потом, джойня CTE managers с таблицей employees, мы находим запись, у которой id совпадает со значением manager последнего выбранного сотрудника. И так до тех пор, пока запрос с inner join не вернёт нам пустой результат. В общем, просто поэкспериментируйте с запросами, пока не осознаете, как оно работает.

Поиск самого верхнего родителя

with recursive tmp as (
    select id, array [id] as path
    from employees
    where manager is null

    union all

    select e.id, e.id || tmp.path
    from employees as e
             inner join tmp on tmp.id = e.manager
)

select id, path[array_upper(path, 1)] as manager_id
from tmp;

----+------------
 id | manager_id
----+------------
  1 |          1
  2 |          1
  3 |          1
  4 |          1
  5 |          1
  6 |          1
  7 |          1
(7 rows)

Мы начинаем с того, что выбираем всех сотрудников без менеджера (верхних родителей всех остальных), потом рекурсивно добавляем id на каждом уровне к массиву path, и, наконец, выбираем id сотрудника и последний элемент массива path с помощью функции array_upper, считая его верхнеуровневым родителем записи.

На этом всё, с рекурсивными запросами мы разобрались, а вот к CTE, думаю, мы ещё вернёмся. Это очень крутая концепция, позволяющая делать больше, чем мы разобрали в этой статье. Благодарю, до встречи! 💫