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

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

1. Нерекурсивный оператор select
2. Union или union all
3. Рекурсивный оператор select

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;

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

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

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

Пример

Первые 10 натуральных чисел

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

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

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

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;

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

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

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

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);

Abhi — начальник, он будет на первом уровне. Bhargav, Chay, Dravid, Erin находятся на следующем уровне, а остальные будут на последнем уровне.

Запрос:

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;

Вывод будет таким:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks