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:
SQL Fiddle