From: David G. Johnston <david.g.johnston@gmail.com>
To: Michael Moore <michaeljmoore@gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: recursive WITH nested union ALL with NOCYCLE logic
Date: Fri, 18 Mar 2016 14:32:42 -0700
Message-ID: <CAKFQuwbd-eTDbHZCxtxg2ro8v3brDAhujcuYTdCUkqqVqNExsQ@mail.gmail.com> (raw)
In-Reply-To: <CACpWLjOS8euH8gAVYQWAfxhgBNvsfKwm1BvBLXyZwzFOpwUMLQ@mail.gmail.com>
References: <CACpWLjOS8euH8gAVYQWAfxhgBNvsfKwm1BvBLXyZwzFOpwUMLQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On Fri, Mar 18, 2016 at 2:06 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> 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.
>
> with
> recursive inn_t(keyv, val, parent) as (
> select * from (
> select key as keyv, val, parent
> from mike_hier hi where hi.key ='aaa'
> union all
> -- get all alt hierarchies
> select child ,null ,null from mike_map ma where ma.parent
> ='aaa' ) gg
> union all
> (
> with xxx as ( select * from inn_t i ) -- only a single reference
> allowed to inn_t
> select * from
> (
> select mh.key , mh.val , mh.parent
> from mike_hier mh
> where mh.parent in (select keyv from xxx) -- normally would
> join inn_t
> union all
> select child ,null ,null
> from mike_map ma
> where 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david.g.johnston@gmail.com, michaeljmoore@gmail.com
Subject: Re: recursive WITH nested union ALL with NOCYCLE logic
In-Reply-To: <CAKFQuwbd-eTDbHZCxtxg2ro8v3brDAhujcuYTdCUkqqVqNExsQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox