PostgreSQL and filtered recursive query

Question

There is an ability to query tree in Oracle with clauses CONNECT BY and START WITH.

For example:

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;

The query result can be simply filtered to show the only nodes which accepted by filter predicate or located on the path to root:

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

A similar select in PostgreSQL will be built with clause WITH RECURSIVE .... As I understand one with-query can not be included in other with-query to get same filtered result as Oracle allows.

How to rewrite the second select in PostgreSQL?..

Nap · Accepted Answer · 2018-10-25 05:20:23Z

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

Of course you can, just write one after the other:

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;

Note the recursive keyword only needs to be stated at the beginning. Regardless on how many recursive CTEs you have (but you need to have at least one in the CTE chain, if you use it).

027 · Accepted Answer · 2015-08-06 12:07:02Z

1

If you have a with query that is calling some function and that function in turn has a with query, then it allows us and not raise any error...in that way we still able to have nested with query.

so typically i created a query using with clause. see the first query below. It has in clause which has select query that is selecting records returned by the functions(my_function). and the function has another hierarchical with query.

Also i do not know what you want to return from your query.so change query as u need.This is just the way to achieve required structure.

Below is the syntax for sql server.Change appropriately for any other database.

with alias_list
(
pig_ear_id,
node_name,
id
) as (
select pig_ear_id, node_name, id
  from hierarchy
 where pig_ear_id = ?
union all
select b.pig_ear_id, node_name, id
  from alias_list a, hierarchy b
 where a.pig_ear_id = b.id
   and id in (select id from my_function('some-pattern')))
select * from alias_list;

==============================================================

create function my_function(@node_name   varchar(40))
returns @temptable table
(
   id varchar(40)
)
as
begin

with alias_list
(
pig_ear_id,
node_name,
id
) as (
select pig_ear_id, node_name, id
  from hierarchy
 where node_name = ?
union all
select b.pig_ear_id, node_name, id
  from alias_list a, hierarchy b
 where a.id = b.pig_ear_id)

insert into @temptable select * from alias_list;
return
end

================================================================

edited Aug 6, 2015 at 12:07

answered Aug 6, 2015 at 11:06

027

1,66114 silver badges23 bronze badges

2 Comments

user330315 Over a year ago

No need for a function. CTEs (aka "with queries") can be nested (actually: chained)

027 Over a year ago

@a_horse_with_no_name , agreed

Collectives™ on Stack Overflow

PostgreSQL and filtered recursive query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related