recursive WITH nested union ALL with NOCYCLE logic

From: Michael Moore <michaeljmoore(at)gmail(dot)com>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: recursive WITH nested union ALL with NOCYCLE logic
Date: 2016-03-18 21:06:16
Message-ID: CACpWLjOS8euH8gAVYQWAfxhgBNvsfKwm1BvBLXyZwzFOpwUMLQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

In the mike_hier table note that:
aaa is the top of my target hierarchy with a children; bbb and ccc
However, from mike_map (see below) , we see that there are two alternative
hierarchies for aaa, namely ddd, and eee .

CREATE TABLE mike_hier
(
key character(3) NOT NULL,
val character varying(5),
parent character(3),
CONSTRAINT key PRIMARY KEY (key)
);
INSERT INTO mike_hier( key, val, parent) VALUES
('aaa','tom',''),
('bbb','cat','aaa'),
('ccc','tad','aaa'),
('ddd','cow',''),
('eee','rat','ddd'),
('fff','fan','ddd'),
('ggg','ram','eee'),
('hhh','sam',''),
('iii','ted','hhh'),
('jjj','abe','hhh'),
('kkk','red',''),
('lll','blu','kkk'),
('mmm','yl','kkk');

CREATE TABLE mike_map
(
key character(3) NOT NULL,
child character(3),
parent character(3),
CONSTRAINT key_const PRIMARY KEY (key)
);
insert into mike_map (key,child,parent) values
('111','ddd','aaa'),
('222','eee','aaa'),
('333','hhh','kkk');

I got pretty much what I want with :
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;

So far so good, but what if I introduce a data loop?
insert into mike_map (key,child,parent) values
('555','aaa','aaa');

at http://www.postgresql.org/docs/9.3/static/queries-with.html I read about:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[g.id],
false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
path || g.id,
g.id = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

I can't figure out how to work this ARRAY concept into my existing query
since I am not actually JOINing in the recursive part of my query.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2016-03-18 21:32:42 Re: recursive WITH nested union ALL with NOCYCLE logic
Previous Message Tom Lane 2016-03-18 13:17:00 Re: Enhancement to SQL query capabilities