How to use WITH RECURSIVE with values in an array?

Question

I'm sure I'm missing something simple here, but I'm trying to use PostgreSQL's WITH RECURSIVE to create a graph of children records.

The data (simplified) looks like this:

CREATE TABLE offspring (parent, children)
AS VALUES
 ( 'aaaaaaaa-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ bbbbbbbb-8865-4b40-a482-78fc8a27d1bb, cccccccc-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
 ( 'bbbbbbbb-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ dddddddd-8865-4b40-a482-78fc8a27d1bb, eeeeeeee-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
 ( 'cccccccc-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ ffffffff-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
 ( 'dddddddd-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 11111111-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
 ( 'eeeeeeee-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 22222222-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
 ( 'ffffffff-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
 ( '11111111-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
 ( '22222222-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] );

I've taken several stabs at it that I thought would work, but nothing has proven successful.

This simplified view reflects just the fields necessary to do the recursion, but when done, I'll also need to check timestamps on each record to filter appropriately, but I'm guessing I can handle that once the basic recursive query is in place.

My goal here is to end up with a single CTE that maps a single parent UUID to a UUID array of all of its children. Neither depth nor order are important in the resulting array.

Any help is appreciated.

• Evan Carroll: are any childrens references by two parents?
• Shane O.: Thinking of this in graph terms, this is a digraph where the leaf nodes (final "children") will have 1:1 "parent" node, but every node that has a down-graph node can have n parents. So the question is: how does one select any node in the graph and navigate toward the leaf nodes, creating an array of all down-graph nodes in the process?

Answer 1

Evidently, the answer required me to step away from it for a little bit.

Here's the query that solved my problem:

WITH RECURSIVE descendents AS (
 SELECT parent,unnest(children) AS child 
 FROM offspring o 
 WHERE parent='aaaaaaaa-8865-4b40-a482-78fc8a27d1bb' 
 UNION ALL
 SELECT o.parent,unnest(o.children) AS child 
 FROM offspring o, descendents d 
 WHERE o.parent = d.child) 
SELECT distinct(child) FROM descendents;

SQL Fiddle