Простейший рекурсивный запрос

Рекурсивные запросы используют в подзапросе данные самого себя.

Используют их чаше всего для построения иерархий данных. Например, для сотрудника вывести список его подчиненных, для этих подчиненных список их подчиненных и т.д.

Простейший пример

Потренируемся на цифрах. Сгенерируем числа от одного до трех:

WITH RECURSIVE lv_recursive (num) as (
  SELECT 1 AS num
    
    UNION ALL
  
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)
SELECT *
  FROM lv_recursive

Обрати внимание, что lv_recursive используется как для названия подзапроса в WITH, так и как таблица в этом же подзапросе.

Чтобы в запросе ссылаться на самого себя, необходимо указать ключевое слово RECURSIVE. В противном случае будет ошибка:

WITH lv_recursive (num) as (
  SELECT 1 AS num
    
    UNION ALL
  
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)
SELECT *
  FROM lv_recursive

error: relation "lv_recursive" does not exist

Рекурсивный подзапрос состоит из двух частей: нерекурсивной, определяющей первоначальный набор данных, и рекурсивной части, выполняющейся итерационно (несколько раз). Нерекурсивная и рекурсивная части разделяются UNION или UNION ALL.

WITH RECURSIVE lv_recursive (num) as (
  -- Нерекурсивная часть
  SELECT 1 AS num
    
    UNION ALL

  -- Рекурсивная часть
  SELECT p.num + 1
    FROM lv_recursive p
   WHERE p.num < 3
)

Результат рекурсивного подзапроса определяется следующим образом:

1. Выполняется нерекурсивная часть подзапроса. Ее результат добавляется в общий результат подзапроса и временную таблицу, для строк которой нужно выполнить рекурсивную часть.

2. Выполняется рекурсивная часть подзапроса. Вместо таблицы во фразе FROM с названием рекурсивного подзапроса (lv_recursive в нашем случае), используются строки из временной таблицы. Результат рекурсивной части добавляется в общий результат подзапроса и становится содержимым временной таблицы. Таким образом, на следующей итерации рекурсивный запрос выполняется для строк, полученных на текущей.

3. Если во временной таблице есть записи, то переходим к шагу 2. Если нет - то рекурсивный подзапрос выполнен.

Разберем на нашем подзапросе

Шаг 1. Выполняется нерекурсивная часть подзапроса.

Общий результат подзапроса:

Временная таблица:

Шаг 2. Рекурсивная часть выполняется 1-й раз

lv_recursive равен

Т.к. 1 < 3, то запрос

SELECT p.num + 1
  FROM lv_recursive p
 WHERE p.num < 3

вернет одну строку с num = 2.

После первого выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица:

Шаг 3. Рекурсивная часть выполняется 2-й раз

lv_recursive равен

Т.к. 2 < 3, то будет возвращена одна строка с num = 3.

После второго выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица:

Шаг 4. Рекурсивная часть выполняется 3-й раз

lv_recursive равен

Т.к. 3 не меньше 3, то результат рекурсивной части не возвращает ни одной строки.

После третьего выполнения рекурсивной части:

Общий результат подзапроса:

Временная таблица пуста.

Т.к. временная таблица пуста, то выполнение рекурсивного подзапроса на этом завершается.

P.S.: Сгенерировать числа в PostgreSQL можно гораздо проще:

SELECT num
  FROM generate_series(1, 5) as num