WITH RECURSIVE в PostgreSQL: рекурсивные табличные выражения

WITH RECURSIVE в PostgreSQL позволяет выполнять рекурсивные запросы, что полезно для работы с иерархическими структурами, такими как деревья и графы.

Использование WITH RECURSIVE

Пример рекурсивного запроса для получения всех потомков определенного узла в иерархической таблице employees:

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1  -- Начальный уровень
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Этот запрос находит всех сотрудников, подчиненных конкретному менеджеру.

Глубина рекурсии и управление

Можно ограничить глубину рекурсии с помощью LIMIT или условия:

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1 FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
    WHERE eh.level < 3  -- Ограничение глубины до 3 уровней
)
SELECT * FROM employee_hierarchy;

Применение WITH RECURSIVE

• Построение иерархических структур (например, организационные схемы).
• Обход графов, например, маршрутов в транспортных системах.
• Вычисление последовательностей и агрегатных данных.