PostgreSQL — рекурсивный запрос с использованием CTE

Строго говоря, этот процесс представляет собой итерацию, а не рекурсию, но терминология RECURSIVE выбрана комитетом по стандартам SQL.

Общая структура рекурсивного запроса Postgres содержит:

1. Нерекурсивный оператор выбора
2. Союз или союз всех
3. Оператор рекурсивного выбора

WITH RECURSIVE name_cte AS (
SELECT statement  /* non-recursive statement */
UNION [ALL]
SELECT statement  /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;

Как работает рекурсивный запрос Postres:

1. Оцените нерекурсивные операторы и создайте временную таблицу
2. Оценить рекурсивные термины и добавить их во временную таблицу
3. Повторяйте шаг 2, пока рабочий стол не станет пустым.

Разница между union и union all заключается в том, что union all разрешает дубликаты. union не допускает дубликатов.

Пример:

WITH RECURSIVE tens AS (
   SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;

Это базовый пример рекурсивного запроса Postres, который печатает первые 10 натуральных чисел.

Рекурсивный запрос Postres для нахождения факториала натурального числа:

WITH RECURSIVE fact (n, factorial)
AS (
    SELECT 1 as n, 5 as factorial
union all
    SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;

Этот запрос выводит две таблицы: одну с первыми пятью натуральными числами, а другую — с вычислениями, которые выполняются для нахождения факториала.

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

Рекурсивный запрос Postres для печати ряда Фибоначчи:

WITH RECURSIVE fibb
AS (
    SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
    SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;

Это печатает ряд Фибоначчи до 10.

С помощью рекурсивного запроса Postgres мы можем найти организационную иерархию:

Чтобы создать таблицу:

INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, "Abhi", NULL),
(2, "Bhargav", 1),
(3, "Chay", 1),
(4, "Dravid", 1),
(5, "Erin", 1),
(6, "Ford", 2),
(7, "Gagan", 2),
(8, "Harry", 3),
(9, "Isaac", 3),
(10, "Jack", 4),
(11, "Kiran", 5);

Абхи - босс, он будет на первом уровне. Бхаргав, Чай, Дравид, Эрин находятся на следующем уровне, а остальные будут на последнем уровне.

Запрос:

WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)  
SELECT * FROM subordinates;

Вывод будет: