Рекурсивные запросы в PostgreSQL (WITH RECURSIVE)

[Envek]

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

WITH RECURSIVE tree AS (
    SELECT
        id, short_name, parent_id, short_name AS sort_string, 1 AS depth
    FROM stations
    WHERE parent_id IS NULL
    UNION ALL
    SELECT
        s1.id, s1.short_name, s1.parent_id,
        tree.sort_string || '|' || s1.short_name AS sort_string, tree.depth+1 AS depth
    FROM
        tree
        JOIN stations s1 ON s1.parent_id = tree.id
)

SELECT depth, short_name, id, parent_id, sort_string FROM tree ORDER BY sort_string ASC;

[qasta]

Ну вы же понимаете, что это работает только до десяти уровней вложенности :)

[Envek]

Ещё нет, не понимаем (там где мы используем, у нас их всего два, да и объектов не очень много).
Расскажете, почему не работает на большом уровне вложенности?

[qasta]

Чуть ошибся — не во вложенности дело, а в именах (думал, что вы в sort_string засовываете depth), но не суть.
У вас просто соединяются строки с разделителем, и, если с ними будет что-то не так (например, в имени элемента встретится символ разделителя), то это может повлиять на сортировку и вы получите данные в неправильном порядке.

Посмотрите на выборку вот такого набора данных:

insert into stations 
values
(1, 'name', null),
(2, 'name|0', null),
(3, 'name|1', null),
(4, 'name0', null),
(5, '1', 1),
(6, '2', 2),
(7, '3', 3);


insert into stations 
values
(11, 'name', null),
(12, 'name2', null),
(13, 'name3', null),
(14, 'name4', null),
(15, '1', 11),
(16, '2', 12),
(17, '3', 13);

Так что
1) выделяйте 2 колонки — одну для «текущего пути» — не забудьте сделать rpad, дабы выровнять длины
2) старайтесь не использовать «синтетические значения» для функционирования системы.
Выглядит, как заплатка, и работает так же.

[Dein777]

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

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

   UNION all
   select *
      from
         (
         with rr as
            (
            select * from r
            )
         SELECT id, parent_id, name
         FROM tmp.geo
         WHERE parent_id IN (  SELECT id FROM rr   )
         ) a
)

SELECT * FROM r;
</source lang>
Часто использую recursive для быстрых выборок по индексу. Получается трудно читаемо, но очень быстро.

[0xA0]

Здравствуйте.
Не подскажете как такие конструкции работают на больших объемах(как по размеру так и по количеству)? Нам, например, пришлось отказаться от JOIN-ов.

[bobzer]

Добавлю: рекурсивных подзапросов может быть несколько в одном SQL-запросе. В начале объявляется WITH RECURSIVE, далее следуют как рекурсивные, так и обычные подзапросы, каждый со своим алиасом. Необязательно осуществлять выборку непосредственно из рекурсивного запроса, его можно, например, джойнить с обычной выборкой из таблицы. Использовал такой подход при формировании отчётов. Выглядит примерно так:

WITH RECURSIVE
tree_query_1 AS (... --рекурсивный запрос
UNION ALL...
),
tree_query_2 AS (... --рекурсивный запрос
UNION ALL...
),
simple_query_1 AS (... --нерекурсивный запрос
--no UNION
)
--далее строим отчёт, используя объявленные выше запросы
SELECT
  t1.field1,
  (SELECT field2 FROM simple_query_1 WHERE id = t1.field2) --используем в блоке SELECT
FROM table_1 t1
INNER JOIN tree_query_1 t2 ON t1.field3 = t2.field4 --используем в JOIN
LEFT JOIN (SELECT * FROM tree_query_2 WHERE...) -- используем в подзапросе в JOIN

Как видно из примера, возможности широкие, и это «рецепты» от не специалиста по СУБД и не знатока PostreSQL…

[zzashpaupat]

Тут еще всякие интересные примеры есть.

[4knowledge]

В документации по СУБД написано, что с помощью рекурсивных запросов можно добираться значительное прироста производительности по сравнению с использованием временных таблиц и хранимых процедур. Насколько верно такое утверждение?

И вот как например можно решить последнюю задачу в статье про уровень вложенности в Европе без рекурсий, в частности с временными таблицами и хранимыми процедурами?

[utrack]

Рекурсия это здорово и удобно, но при поиске иерархичных структур на больших объёмах данных\большой вложенности очень быстро проседает производительность — в таких случаях удобней создавать суррогатное поле-массив айдишников с путём элемента от корня и изменять его триггерами. Если интересно — могу написать статью.
Правда, такой подход должен плохо работать при mods > reads из-за постоянного переписывания индексов. Тут остаётся только сесть в угол и бояться :)

[stalkerg]

тут не честная рекурсия так что должно быть всё ок…

[varanio]

Ждем статью )

[3draven]

Большущее всем спасибо за простые пояснения. Теперь ясно, что в Postgres есть цикл while с условием :) Пригодилось только что для построения кучки деревьев с убиранием циклических зависимостей и прочими радостями.