Рекурсивные 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 будут сделаны дополнительные ключевые слова. Но об этом позже )
Рекурсивные 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 будут сделаны дополнительные ключевые слова. Но об этом позже )