I have a table
| uuid | name | parent_id |
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
For a query on
uuid3, I want to return
and for a query on
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
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 ;