Postgres Recursively resolve tree relation with left joins

Question

I have a table

| uuid | name | parent_id |

Where parent_id is nullable.

I want to write a query which given a specific row, will recursively traverse a path up until a row with null parent_id is reached and join (so I can cast it into a recursive class in the application)

So if I have

uuid	name	parent_id
uuid1	name1	null
uuid2	name2	uuid1
uuid3	name3	uuid2

For a query on uuid3, I want to return

uuid	name	parent_id	b.uuid	b.name	b.parent_id	c.uuid	c.name	c.parent_id
uuid3	name3	uuid2	uuid2	name2	uuid1	uuid1	name1	null

and for a query on uuid2

uuid	name	parent_id	b.uuid	b.name	b.parent_id
uuid2	name2	uuid1	uuid1	name1	null

When I started reading up on recursive queries, not surprisingly I hit WITH RECURSIVE. I tried playing around with it a little bit (see some attempts at the bottom) but it seems like the restriction on the recursive query and non-recursive query makes it unfit for this purpose where the number of columns is not known in advance. It is more for cases, when you want to trace rows and return them as individual results.

Is something like this actually possible to do in Postgres? Would appreciate any hints

Thank you

P.S.: I appreciate any concerns/comments regarding the performance implications of such a query but the table is guaranteed to be very small (< 1000 rows) and the max depth of such a relation is guaranteed to be < 4 levels

I am also specifically interested if this is possible in Postgres. I am aware that graph based databases like Neo4j serve exactly these purposes.

WITH RECURSIVE rg AS (
 SELECT
 uuid, name, parent_id,
 FROM
 genres 
 WHERE uuid = '....'
 UNION
 SELECT
 g.uuid, g.name, g.parent_id, 
 FROM
 genres g
 LEFT JOIN genres b ON g.parent_id = b.uuid 
) SELECT * FROM rg;

WITH RECURSIVE rg AS (
 SELECT uuid, name, parent_id
 FROM genres
 WHERE uuid = '....'
 UNION 
 SELECT child.uuid, child.name, child.parent_id
 FROM genres AS child
 JOIN pc ON pc.uuid = child.parent_id 
)
SELECT rg.*
FROM genres rg
 JOIN b on rg.parent_id = b.uuid
;

• Vérace: 2 & 3 are each other's parent!
• sinanspd: @Vérace-СлаваУкраїні that was a typo. My apologies. I updated the table. The parent/child relation is linear/non-cyclic otherwise this wouldn't work
• Akina: You want to receive dynamic output structure (different amount of columns depends on the data). This cannot be performed in a query. Either use dynamic SQL or (preferred) return record-per-row data (as shown in "So if I have") and transform it on the client side.
• sinanspd: @Akina Thank you. That is what I suspected but I am not familiar with internals of sql enough to definitively decide so wanted to ask and make sure. Yeah, I can do it in 2-3 lines in Scala. I was just curious if it can be done in Postgres so I could delegate the optimizations to the DB

Answer 1

While not an answer to your question as stated (dynamic columns) you could use a recursive query to return columns that contain arrays of parents. Something like (adjust as needed):

WITH RECURSIVE toc AS (
 SELECT genres.uuid,
 genres.name,
 '{}'::uuid[] AS parents,
 genres.parent_id,
 1 AS path_depth,
 ARRAY [ dense_rank () OVER (
 ORDER BY genres.name ) ] AS outln,
 ARRAY[genres.uuid] AS id_path,
 ARRAY[genres.name] AS name_path
 FROM genres
 WHERE genres.parent_id IS NULL
 UNION ALL
 SELECT genres.uuid,
 genres.name,
 parents || genres.parent_id,
 genres.parent_id,
 ( q.path_depth + 1 ) AS path_depth,
 ( q.outln || dense_rank () OVER (
 PARTITION BY genres.parent_id
 ORDER BY genres.name ) ) AS outln,
 q.id_path || genres.uuid,
 q.name_path || genres.name
 FROM genres
 JOIN toc q
 ON ( genres.parent_id = q.uuid
 AND NOT genres.uuid = ANY ( q.parents ) ) -- avoid cyclic references
)
SELECT toc.uuid,
 toc.name,
 toc.parent_id,
 toc.parents,
 toc.path_depth,
 toc.id_path,
 toc.name_path,
 array_to_string ( toc.outln, '.'::text ) AS outln,
 array_to_string ( toc.id_path, ' > '::text ) AS id_path_str,
 array_to_string ( toc.name_path, ' > '::text ) AS name_path_str
 FROM toc

• sinanspd: Thank you! This is quite the query. I am rather new when it comes to raw SQL so I am going to need some time digest what is going on here. I will once again note for future visitors of the question that this doesn't fully solve the original problem but accept this as it does provide an interesting and viable alternative.