Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ah21p-00025z-TU for pgsql-sql@arkaria.postgresql.org; Fri, 18 Mar 2016 21:33:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ah21p-0007dC-8z for pgsql-sql@arkaria.postgresql.org; Fri, 18 Mar 2016 21:33:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ah20k-0006Sc-S9 for pgsql-sql@postgresql.org; Fri, 18 Mar 2016 21:32:47 +0000 Received: from mail-ob0-x236.google.com ([2607:f8b0:4003:c01::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ah20h-0003Q4-TK for pgsql-sql@postgresql.org; Fri, 18 Mar 2016 21:32:45 +0000 Received: by mail-ob0-x236.google.com with SMTP id fz5so129383976obc.0 for ; Fri, 18 Mar 2016 14:32:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=danGNhIGF7OP3oCq47pFEw23Qd+Qgwd3QsQjVF6PuHk=; b=lwHBPtSCgkBG9fjbTCkU86O4cfO+yUHijKvRxP7U9kflINl3M6ztW5E1pAl0ix2i9K 05pf01+Sqotum1Gi+gM7hvU8EEFoILYQB+Wy9C+gea6pmdyFtC471nXvVF1+t81FPSTt /DxE0i4yxZkHjJpgKzBigOdSUyMuEvXrCHsqkgl2yKP7xCSOsve0KyipRVcrqKj07Wn6 gcpBtjEJrAaQ4ysXWkZ99LkyTJWXvUlqD0vh2n2LCoHotPupc3bQYXcEFPljvcxIcQsE T5u2W+G4onXbHuNYmRSzA133jDjcDyyBAkFJUJKWtedrcHpf+JMzV6PAPKrdIJuS+Lhy 7bqg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=danGNhIGF7OP3oCq47pFEw23Qd+Qgwd3QsQjVF6PuHk=; b=YmKunvOK6r8k/04pUw7OjSuiImYaKRslJuFCqsSldJEP891lPNDG6vyGe5AbM5yjA+ io9v9yMvp9b1NwwBPO+Bbw25hVVGF47I2JMQUH1tOZ6zoKfmA1zvJ0YTOVQA/F66NPSM E7C4QQl5CyHnkJUKv7bHEu2HAugiUA5+mnIMS6ADDIMZ1bllIz/NeOnDSKKojduojaEu fIcVdo8HnQuHJrnPS+YhNYOoYhT1bSaxCWr7xi4whE3FBZGq0ryaEfbCKSh4HXDPOyDK CIqzJkZc+TmaFnJhhXjUnZ+lKTZ/G3QL9o1arc2/xLMzezqxwXHUDdQPLUAnXhzZh5k+ 76PQ== X-Gm-Message-State: AD7BkJKXd5bJNysnTMrBvnfzKn50PyoqSv7eZZSxsBRMtlPe1A0SSEgAe1k7cxciBENXN3uh8oYVLYg4rvMwMg== MIME-Version: 1.0 X-Received: by 10.60.50.98 with SMTP id b2mr10996070oeo.38.1458336762778; Fri, 18 Mar 2016 14:32:42 -0700 (PDT) Received: by 10.157.44.183 with HTTP; Fri, 18 Mar 2016 14:32:42 -0700 (PDT) In-Reply-To: References: Date: Fri, 18 Mar 2016 14:32:42 -0700 Message-ID: Subject: Re: recursive WITH nested union ALL with NOCYCLE logic From: "David G. Johnston" To: Michael Moore Cc: postgres list Content-Type: multipart/alternative; boundary=001a11c1e276b55b79052e597ce7 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a11c1e276b55b79052e597ce7 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Fri, Mar 18, 2016 at 2:06 PM, Michael Moore 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 retu= rn > 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 =3D'aaa' > union all > -- get all alt hierarchies > select child ,null ,null from mike_map ma where ma.parent > =3D'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 :) =E2=80=8Bwith recursive --applies to the second CTE really but placed at th= e 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 =3D'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 =3D'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 =3D 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; =E2=80=8BI haven't yet coded a variation of this query that used the path a= rray 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. =E2=80=8B =E2=80=8B =E2=80=8B --001a11c1e276b55b79052e597ce7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Fri, Ma= r 18, 2016 at 2:06 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
=
I have tw= o tables, 1 is a hierarchical table and the other a map to alternative hier= archies. Given a starting node, I need to be able to return the hierarchy a= nd all related hierarchies.=C2=A0

with
=C2=A0 =C2=A0 recursive inn_t(keyv, val, par= ent) as (
= =C2=A0 =C2=A0 =C2=A0 =C2=A0select * from (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 select k= ey as keyv, val, parent=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from mik= e_hier hi where hi.key =3D'aaa'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 union all
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- get all alt hierarchies
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 select child ,null ,null from mike_map ma =C2=A0where ma.par= ent =3D'aaa' ) gg
=C2=A0 =C2=A0 union all
=C2=A0 =C2=A0 (
=C2=A0 =C2=A0 =C2=A0 =C2=A0with xxx as ( s= elect * from inn_t i ) -- only a single reference a= llowed to inn_t
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0select * from=C2=A0
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 select mh.key =C2=A0 =C2=A0 , mh.= val =C2=A0 =C2=A0 =C2=A0 =C2=A0, mh.parent=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0from mike_hier mh
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where =C2=A0mh.parent= in (select keyv from xxx) -- normally would join i= nn_t
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 union all
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0select child ,null ,null=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from mike_m= ap ma =C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where ma.parent in (select keyv = from xxx) =C2=A0-- normally would join inn_t=
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ) unionall
=C2=A0 =C2=A0 =C2=A0 )
=C2=A0 =C2=A0 =C2=A0)=C2=A0
=C2=A0 =C2=A0select d= istinct * from inn_t where val is not null;


Where shoul= d I send the bill for the pain=C2=A0relievers :)
=E2=80=8Bwith recursive --applies to the second CTE really b= ut placed at the top by convention (maybe by rule)
inn_t(keyv, val,= parent) as ( =C2=A0--not recursive, no reference to inn_t in this CTE
-- Given a base tree lets return all rows where it is the primary...
=C2=A0 =C2=A0 =C2=A0 =C2=A0select * from (
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 select key as keyv, val, parent=C2=A0
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from mike_hier hi where hi.key =3D= 9;aaa'
-- ...as well as the primary rows for any of its alises = (derived though they may be it should work)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 union all
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -= - get all alt hierarchies
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= select child ,null ,null from mike_map ma =C2=A0where ma.parent =3D'aa= a' ) 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.=C2=A0 On the next pass the origi= nal parents will be skipped
-- because they were already processed = but all of these newly added children will be
-- put through the wr= inger to find their children.
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sel= ect * from inn_t i =C2=A0 --initial condition is a complex query so simplif= y the recursive portion by referecing a CTE
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0UNION ALL
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 select mh= .key =C2=A0 =C2=A0 , mh.val =C2=A0 =C2=A0 =C2=A0 =C2=A0, mh.parent=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from mike_hier mh
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0join inn_t ON (mh.parent =3D 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 s= hould
-- be redundant.
=C2=A0 =C2=A0select * from recurse_h= ere_instead where val is not null;

=E2=80=8BI haven&= #39;t yet coded a variation of this query that used the path array and cycl= e-avoidance logic so I'm leaving that open for the moment.=C2=A0 Now th= at this is written more correctly incorporating that from other's examp= les should be easier.

David J.
=E2=80=8B
=E2=80=8B
=E2=80=8B
--001a11c1e276b55b79052e597ce7--