Search code examples
postgresqlplpgsqlcommon-table-expressionrecursive-query

How to return different format of records from a single PL/pgSQL function?

I am a frontend developer but I started to write backend stuff. I have spent quite some amount of time trying to figure out how to solve this. I really need some help.

Here are the simplified definitions and relations of two tables:

Relationship between tables

CREATE TABLE IF NOT EXISTS items (
    item_id       uuid          NOT NULL DEFAULT gen_random_uuid() ,
    parent_id     uuid                   DEFAULT NULL              ,   
    parent_table  parent_tables NOT NULL
);

CREATE TABLE IF NOT EXISTS collections (
    collection_id uuid          NOT NULL DEFAULT gen_random_uuid() ,
    parent_id     uuid                   DEFAULT NULL
);

Our product is an online document collaboration tool, page can have nested pages.

I have a piece of PostgreSQL code for getting all of its ancestor records for given item_ids.

WITH RECURSIVE ancestors AS (
    SELECT *
    FROM items
    WHERE item_id in ( ${itemIds} )

    UNION

    SELECT i.*
    FROM items i
    INNER JOIN ancestors a ON a.parent_id = i.item_id
)
SELECT * FROM ancestors

It works fine for nesting regular pages, But if I am going to support nesting collection pages, which means some items' parent_id might refer to "collection" table's collection_id, this code will not work anymore. According to my limited experience, I don't think pure SQL code can solve it. I think writing a PL/pgSQL function might be a solution, but I need to get all ancestor records to given itemIds, which means returning a mix of items and collections records.

So how to return different format of records from a single PL/pgSQL function? I did some research but haven't found any example.

Solution

  • You can make it work by returning a superset as row: comprised of item and collection. One of both will be NULL for each result row.

    WITH RECURSIVE ancestors AS (
   SELECT 0 AS lvl, i.parent_id, i.parent_table, i AS _item, NULL::collections AS _coll
   FROM   items i
   WHERE  item_id IN ( ${itemIds} )

   UNION ALL -- !
   SELECT lvl + 1, COALESCE(i.parent_id, c.parent_id), COALESCE(i.parent_table, 'i'), i, c
   FROM   ancestors        a 
   LEFT   JOIN items       i ON a.parent_table = 'i' AND i.item_id = a.parent_id
   LEFT   JOIN collections c ON a.parent_table = 'c' AND c.collection_id = a.parent_id
   WHERE  a.parent_id IS NOT NULL
   )
SELECT lvl, _item, _coll
FROM   ancestors
--     ORDER BY ?

    db<>fiddle here

    UNION ALL, not UNION.

    Assuming a collection's parent is always an item, while an item can go either way.

    We need LEFT JOIN on both potential parent tables to stay in the race.

    I added an optional lvl to keep track of the level of hierarchy.

    About decomposing row types: