Re: recursive WITH nested union ALL with NOCYCLE logic - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: recursive WITH nested union ALL with NOCYCLE logic |
| Date | |
| Msg-id | CAKFQuwbd-eTDbHZCxtxg2ro8v3brDAhujcuYTdCUkqqVqNExsQ@mail.gmail.com Whole thread Raw |
| In response to | recursive WITH nested union ALL with NOCYCLE logic (Michael Moore <michaeljmoore@gmail.com>) |
| Responses |
Re: recursive WITH nested union ALL with NOCYCLE logic
|
| List | pgsql-sql |
I have two tables, 1 is a hierarchical table and the other a map to alternative hierarchies. Given a starting node, I need to be able to return the hierarchy and all related hierarchies.withrecursive inn_t(keyv, val, parent) as (select * from (select key as keyv, val, parentfrom mike_hier hi where hi.key ='aaa'union all-- get all alt hierarchiesselect child ,null ,null from mike_map ma where ma.parent ='aaa' ) ggunion all(with xxx as ( select * from inn_t i ) -- only a single reference allowed to inn_tselect * from(select mh.key , mh.val , mh.parentfrom mike_hier mhwhere mh.parent in (select keyv from xxx) -- normally would join inn_tunion allselect child ,null ,nullfrom mike_map mawhere ma.parent in (select keyv from xxx) -- normally would join inn_t) unionall))select distinct * from inn_t where val is not null;
Where should I send the bill for the pain relievers :)
with recursive --applies to the second CTE really but placed at the top by convention (maybe by rule)
inn_t(keyv, val, parent) as ( --not recursive, no reference to inn_t in this CTE
-- Given a base tree lets return all rows where it is the primary...
select * from (
select key as keyv, val, parent
from mike_hier hi where hi.key ='aaa'
-- ...as well as the primary rows for any of its alises (derived though they may be it should work)
union all
-- get all alt hierarchies
select child ,null ,null from mike_map ma where ma.parent ='aaa' ) gg
), recurse_here_instead AS (
-- Now for each of the those primary rows locate in mike_heir locate the direct descendants
-- and add them to the working set. On the next pass the original parents will be skipped
-- because they were already processed but all of these newly added children will be
-- put through the wringer to find their children.
select * from inn_t i --initial condition is a complex query so simplify the recursive portion by referecing a CTE
UNION ALL
select mh.key , mh.val , mh.parent
from mike_hier mh
join inn_t ON (mh.parent = inn_t.keyv)
)
-- got rid of distinct...honestly not positive why but I suspect if you write the query correct DISTINCT on the outer layer should
-- be redundant.
select * from recurse_here_instead where val is not null;
I haven't yet coded a variation of this query that used the path array and cycle-avoidance logic so I'm leaving that open for the moment. Now that this is written more correctly incorporating that from other's examples should be easier.
David J.