NOCYCLE in Postgres

Question

I have a Oracle query with a NOCYCLE clause which I have to translate to Postgres:

SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID  
FROM FG t
START WITH t.Parent_filter_group_id is null 
CONNECT BY NOCYCLE PRIOR t.FILTER_GROUP_ID = t.PARENT_FILTER_GROUP_ID 

I have converted this one with the help of the question and answer in connect_by_root equivalent in postgres

as

with recursive fg_tree as (
select FG_ID,
       FG_ID as fg
from  FG
where Parent_filter_group_id is null 

union all 
select c.FG_ID,
p.fg
from FG c join fg_tree p on p.FG_ID = PARENT_FILTER_GROUP_ID
)
select * from fg_tree
order by FG_ID

but in this there is no clause for NOCYCLE if the parent is also one of the children then this query will return error.

Tenebrae

The documentation on recursive queries gives an example of using an array to prevent cycles: postgresql.org/docs/9.3/static/queries-with.html. – Sofar

Thanks@Gordon Linoff documentation is usefull – Tenebrae

Answer 1

You can collect the IDs for each level and then join on the condition that the "current" id is not contained in the path:

with recursive fg_tree as (
  select FG_ID,
         FG_ID as fg, 
         array[fg_id] as path
  from  FG
  where Parent_filter_group_id is null 

  union all 

  select c.FG_ID,
         p.fg, 
         p.fg||c.fg_id
  from FG c 
    join fg_tree p on p.FG_ID and c.fg_id <> ALL (p.path)
)
select fg_id, fg 
from fg_tree
order by filter_group_id

Gader

Answer 2

oracle version:

SELECT     o.object_id, p.plugin_id AS plugin_id, LEVEL, CONNECT_BY_ISCYCLE "Cycle"
FROM       sst_cycle_obj o LEFT JOIN sst_cycle_devplug p ON p.device_id = o.object_id
WHERE      CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE o.object_id = PRIOR p.plugin_id
START WITH o.object_id = 11

create table sst_cycle_obj
    (object_id numeric(10))


create table sst_cycle_devplug
(device_id numeric(10)
,plugin_id numeric(10)) 


insert into sst_cycle_obj (object_id) 
(select 11 from dual
union all
select 12 from dual
union all
select 13 from dual)

insert into sst_cycle_devplug (device_id,plugin_id) 
(select 11, 12 from dual
union all
select 12, 13 from dual
union all
select 13,11 from dual)

-->NOCYCLE2 CONNECT_BY_ISCYCLE "Cycle" Postgresql version
WITH recursive ncot
    AS
         (SELECT a.device_id,a.plugin_id,('{'||a.device_id||'}')::numeric[] as PATH, 0 AS cycle1, 1::int as level
          FROM   ( select o.object_id device_id, p.plugin_id as plugin_id
                    from sst_cycle_obj o 
                    left join sst_cycle_devplug p on p.device_id=o.object_id 
                     ) a
          WHERE  a.device_id = 11
          UNION ALL
          SELECT objt.device_id,objt.plugin_id, ncot.path||objt.device_id::numeric as PATH, CASE WHEN objt.plugin_id = any(ncot.path) THEN 1 else 0 END AS cycle1, ncot.level + 1 as level
          FROM   ( select o.object_id device_id, p.plugin_id as plugin_id
                    from sst_cycle_obj o 
                    left join sst_cycle_devplug p on p.device_id=o.object_id 
                     ) objt
                 JOIN ncot ON objt.device_id = ncot.plugin_id and objt.device_id <> ALL (ncot.path)
                 )
 SELECT device_id,plugin_id,PATH,cycle1,level
 FROM   ncot    
 WHERE cycle1=1
--< 

Sideboard