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 | CAKFQuwYzjXpuW_vq_ktSnPT2w9_24_Xn+Bbzw5NrDLPT_PUFLg@mail.gmail.com Whole thread Raw |
| In response to | Re: 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 |
David,If I am understanding you correctly, you are assuming that alternative hierarchies are mapped to only ROOT level hierarchies. It's a reasonable assumption on your part given my illustration only covered this use case. But lets add another mapping.insert into mike_map (key,child,parent) values('555','kkk','bbb');This creates an alternative hierarchy that branches from a CHILD (bbb) of the ROOT (aaa) hierarchy.So I think I still need the UNION ALL inside the recursive part.
You are correct about my assumed premise - and I'm not sure I want to deal with the brain damage that would result from exploring the model you are working with...
If you can change how you model "mike_map" this should be workable.
The following provides the desired result for the original data as well as, I think, the correct result when adding you kkk/bbb alias.
I think this gets you closer..but probably not all of the way to your goal.
My specific concern is that none of these queries (including your original) have considered "ddd,cow,null" as a valid result...because it lacks a parent.
I'll leave to you to copy-paste and make it readable...
WITH recursive
mike_map_alt (parent, children) AS (
--using an array here is probably not mandatory but makes the logic below simpler by use of unnest()
VALUES ('aaa',ARRAY['ddd','eee']::text[]), ('bbb',ARRAY['kkk']::text[])
), mike_hier_aliased AS (
--here we "canonical-ize" the data so that for each row we know which direct aliases are in play
SELECT mh.key::text AS key, mh.val, mh.parent::text AS parent,
mh.key::text || COALESCE(mp.children, ARRAY[]::text[]) AS key_aliases,
mh.parent::text || COALESCE(mk.children, ARRAY[]::text[]) AS parent_aliases
FROM mike_hier mh
LEFT JOIN mike_map_alt mp ON (mh.key = mp.parent)
LEFT JOIN mike_map_alt mk ON (mh.parent = mk.parent)
)
--SELECT * FROM mike_hier_aliased --used for debugging the transformed data
, recursive_part (my_key, my_val, my_parent) AS (
-- unnest the for the original row output the value; we start at the root so parent should be null for all records
-- for aliases we don't know what their root entry is yet so simply record null for value
-- and let the first iteration pick up the real record
-- this could be altered to left join the mike_heir_aliased table records containing null parents
-- and coalesce the joined "val" column. This would cause "cow" from the "ddd' record to be added to the
-- initial "ddd" unnested alias record.
SELECT me_key, CASE WHEN key = me_key THEN val ELSE null::text END AS my_val, null::text AS my_parent FROM (
SELECT *, unnest(key_aliases) AS me_key FROM mike_hier_aliased WHERE mike_hier_aliased.key = 'aaa'
) src
-- on each iteration we again have to look for aliases and create
-- dummy records for them (via unnest) for the next iteration
UNION --let the recursive part take care of duplicates
SELECT me_key,
CASE WHEN key = me_key THEN val ELSE null::text END AS my_val,
CASE WHEN key = me_key THEN parent ELSE null::text END AS my_parent
FROM (
SELECT mha.key, mha.val, mha.parent, unnest(mha.key_aliases) AS me_key
FROM mike_hier_aliased mha
JOIN recursive_part rp
ON (rp.my_key = mha.parent) --fails to match mha.(ddd,cow,null) though rp.(ddd,null,null) is present
) rec_src
)
SELECT * FROM recursive_part WHERE my_val IS NOT NULL
--our dummy records all have null for my_val so lets remove them
David J.