Рекурсивные CTE в Postgresql

Рекурсивные CTE нужны для работы с хитросвязанными между собой строками, например, для работы с деревьями

К примеру, у вас есть записи ( id, parent_id, name ), и вам надо построить поддерево для определенного id.

Как это всё работает.

На самоме деле, название "рекурсивные CTE" не совсем удачное, больше подошло бы "итеративное CTE".

Пример:

WITH RECURSIVE r AS (
SELECT id, parent_id, name
FROM geo
WHERE parent_id = 4

UNION

SELECT geo.id, geo.parent_id, geo.name
FROM geo
JOIN r
ON geo.parent_id = r.id
)
SELECT * FROM r;

Рекурсивный подзапрос должен состоять из двух частей, объединенных UNION

Первая часть (та что до UNION) - это так называемый anchor, он выполняется в начале, это первая итерация.

Далее в цикле выполняется вторая часть. Это очередная итерация, которая работает в связке с предыдущей (джойнится на результат предыдущей итерации).

Это всё работает до тех пор, пока очередная итерация не вернёт 0 строк результата.

Т.е. получается, что мы в первом запросе нашли все элементы где parent_id = 4, потом нашли элементы, где parent_id - это id из первого запроса, и т.д., пока не кончится.

В случае, если в таблице граф с циклическими связями, то эти итерации не кончатся никогда )

Для того, чтобы бороться с этой проблемой в postgres 14 будут сделаны дополнительные ключевые слова. Но об этом позже )