Postgres中的NOCYCLE

本文介绍了Postgres中的NOCYCLE的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有NOCYCLE子句的Oracle查询，我必须将其转换为Postgres:

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

我已经在问答的帮助下将其转换为 等同于postgres的connect_by_root

I have converted this one with the help of the question and answer in 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的子句，如果父级也是子级之一，则此查询将返回错误.

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

推荐答案

您可以收集每个级别的ID，然后在路径中不包含当前" ID的条件下加入:

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