Postgres中的NOCYCLE

Question

我有一个Oracle查询，其中包含了NOCYCLE子句，现在需要将其翻译成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 

我在connect_by_root equivalent in postgres的问答帮助下，将这个项目进行了转换。

转换后结果如下：

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

但是在这个查询中没有针对 NOCYCLE 的子句，如果父节点也是其中一个子节点，那么这个查询将会返回错误。

- SarthAk

---

递归查询的文档提供了使用数组来防止循环的示例：http://www.postgresql.org/docs/9.3/static/queries-with.html。 - Gordon Linoff

谢谢@Gordon Linoff的文档很有用。 - SarthAk

Answer 1

您可以收集每个级别的ID，然后在“当前” ID不包含在路径中的条件下进行连接：

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

- user330315

Answer 2

Oracle版本：

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
--< 

- sst