Postgresql – How to use WITH RECURSIVE with values in an array

postgresqlrecursive

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.

Best Answer

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