0

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.

  • are any childrens references by two parents? – Evan Carroll Sep 6 '17 at 18:23
  • 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? – Shane O. Sep 7 '17 at 11:57
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.