1

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
;
4
  • 2 & 3 are each other's parent!
    – Vérace
    Jun 20, 2022 at 2:06
  • @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
    – sinanspd
    Jun 20, 2022 at 2:19
  • 1
    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.
    – Akina
    Jun 20, 2022 at 4:46
  • @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
    – sinanspd
    Jun 20, 2022 at 7:29

1 Answer 1

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
1
  • 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.
    – sinanspd
    Jun 22, 2022 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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