oracle - PostgreSQL 和过滤递归查询

Question

Oracle 中可以使用子句CONNECT BY 和START WITH 查询树。

例如:

SELECT RPAD (' ', (LEVEL - 1) * 4) || node_name AS node, LEVEL
  FROM hierarchy
  START WITH NVL (pig_ear_id, 0) = 0
  CONNECT BY PRIOR id = pig_ear_id;

可以简单地过滤查询结果以显示唯一被过滤谓词接受或位于根路径上的节点:

SELECT RPAD (' ', (LEVEL - 1) * 4) || node_name AS node, LEVEL
  FROM hierarchy
  START WITH NVL (pig_ear_id, 0) = 0
  CONNECT BY PRIOR id = pig_ear_id AND id IN (
                 SELECT id
                   FROM hierarchy
                   START WITH node_name = 'some-pattern'
                   CONNECT BY PRIOR pig_ear_id = id
               );

PostgreSQL 中的类似选择将使用子句 WITH RECURSIVE ... 构建。据我了解，一个 with-query 不能包含在其他 with-query 中以获得与 Oracle 允许的相同的过滤结果。

如何在 PostgreSQL 中重写第二个选择？..

Answer 1

As I understand one with-query can not be included in other with-query

当然可以，一个接一个写就可以了:

with recursive valid_nodes as (

   -- this is the inner "CONNECT BY" query from your example
   select id 
   from hierarchy
   where node_name = 'some-pattern'
   union all
   select c.id
   from hierarchy c
     join valid_nodes p on c.id = p.pig_ear_id

), final_tree as (

   -- this is outer query from your example

   select node_name as node, 1 as level
   from hierarchy
   where NVL (pig_ear_id, 0) = 0

   union all

   select c.node_name, p.level + 1
   from hierarchy c
     join final_tree p on p.id = c.pig_ear_id
   where id in (select id from valid_nodes) -- and here we re-use the previous CTE

)
select rpad(node, level - 1)||node, level
from final_tree;

注意 recursive 关键字只需要在开头声明。无论您有多少递归 CTE(但您需要在 CTE 链中至少有一个，如果您使用它)。