Рекурсивный запрос в postgresql

#база_данных #postgresql #рекурсия

Есть такая таблица   

Мне надо получить иерархию в виде 

Сделал рекурсивный запрос 

with  recursive rec(scopename , parentname ) as (
   select scopename , parentname  from _geoimage_scope 
   union all
   select rec.scopename , rec.parentname  from rec,_geoimage_scope where rec.scopename
= _geoimage_scope.parentname
)

select * from rec where parentname is null 


Но не выходит,  получается бесконечная рекурсия. Кто может подсказать, помочь? Пользуюсь
 postgresql
    

Ответы

Ответ 1

У Вас получился бесконечный запрос потому, что (представим пример на одной из записей):


На первом шаге Вы отобрали запись scopename='utilitiesconsumption' И теперь в rec
находится эта одна запись.
На втором шаге Вы берете полученную запись и делаете запрос вида (примерно)


   select rec.scopename, rec.parentname
    from
    (
      select cast('utilitiesconsumption' as text) scopename, null parentname 
    ) rec, _geoimage_scope
    where rec.scopename = _geoimage_scope.parentname


Который возвращает Вам точно такую же запись, что сейчас есть в rec, только, в Вашем
случае, четыре раза. 
Получается, что теперь у Вас в rec находится 5 одинаковых записей. 


На третьем шаге Вы берете следующую запись из rec и, т.к. она у Вас точно такая же
как и первая запись, то полностью повторяется шаг 2. В результате чего количество записей
в rec становится уже 9 (и все они одинаковые) и так до бесконечности.


Вам должен помочь такой запрос:

with recursive rec(scopename, parentname ) 
as 
( 
  select scopename, parentname 
  from _geoimage_scope
  where parentname is null
  union all 
  select _geoimage_scope.scopename, _geoimage_scope.parentname 
  from _geoimage_scope, rec where _geoimage_scope.parentname = rec.scopename 
)
select * from rec 


Тестовый пример можно посмотреть здесь