Можно ли делать рекурсивные запросы в PostgreSQL?

Можно ли делать рекурсивные запросы в PostgreSQL?

Да, в PostgreSQL можно и нужно делать рекурсивные запросы. Это одна из мощнейших возможностей СУБД, реализованная через механизм Common Table Expressions (CTE) с использованием ключевого слова RECURSIVE. Рекурсивные запросы предназначены для работы с иерархическими или древовидными структурами данных, которые трудно или неэффективно обрабатывать обычными SQL-операторами.

Как работают рекурсивные CTE

Рекурсивный запрос состоит из двух основных частей:

1. Не рекурсивная часть (anchor member): Базовый запрос, который возвращает начальный набор строк.
2. Рекурсивная часть (recursive member): Запрос, который ссылается на само CTE, обрабатывая результаты предыдущей итерации. Выполняется до тех пор, пока не вернёт пустой результат.

Синтаксис выглядит так:

WITH RECURSIVE имя_cte AS (
    -- Не рекурсивная часть (начало)
    SELECT ... FROM таблица WHERE условие_начала
    UNION ALL
    -- Рекурсивная часть
    SELECT ... FROM таблица
    JOIN имя_cte ON условие_рекурсии
)
SELECT * FROM имя_cte;

Практический пример: иерархия сотрудников

Рассмотрим классический пример таблицы сотрудников, где каждый имеет ссылку на руководителя.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
('Алексей', NULL),      -- Гендиректор
('Борис', 1),
('Виктор', 1),
('Галина', 2),
('Дмитрий', 2),
('Елена', 3);

Чтобы получить всех подчинённых сотрудника с id = 1 (включая всех на всех уровнях вложенности), используем рекурсивный запрос:

WITH RECURSIVE subordinates AS (
    -- Базовый случай: начальный сотрудник
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1
    
    UNION ALL
    
    -- Рекурсивный шаг: находим подчинённых для текущей итерации
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, id;

Результат покажет всю иерархию подчинённых с указанием уровня вложенности.

Важные ограничения и особенности

• Обязательное использование UNION ALL: Рекурсивная часть должна использовать UNION ALL, а не UNION.
• Предотвращение бесконечной рекурсии: PostgreSQL автоматически останавливает выполнение при обнаружении цикла. Однако для дополнительного контроля можно использовать ограничение по глубине в рекурсивной части:

  WHERE s.level < 10 -- Ограничиваем глубину 10 уровнями
  

• Производительность: Для больших иерархий критически важно иметь корректные индексы на столбцах, используемых в условии соединения (например, manager_id).
• Альтернативы: Для некоторых сценариев работы с иерархиями могут быть более эффективны:

    *   **`ltree`** — расширение для работы с метками путей в деревьях.
    *   **Вложенные множества (Nested Sets)** — альтернативная модель хранения иерархий.
    *   **Материализованные пути** — хранение полного пути в виде строки или массива.

Заключение

Рекурсивные запросы — не просто допустимая, а фундаментальная возможность PostgreSQL для решения задач, связанных с иерархическими данными: организационные структуры, деревья комментариев, графы маршрутов, категории товаров. Они демонстрируют мощь декларативного подхода SQL, где разработчик описывает что нужно получить, а СУБД оптимизирует как это выполнить. Однако важно помнить о потенциальных проблемах производительности на очень больших или циклических графах и правильно проектировать структуры данных и индексы.