Result Set parameter to a PostgreSQL function

Question

I was wondering if there's a way of providing a result set as a parameter to a PostgreSQL function. The answer is probably a "no" but it doesn't hurt to ask. Maybe some ideas can come out of it.

I have the logic to format a result set as a tree for simple visualization purposes and I wanted to add it as a function (format_tree()) in the database.

For example if a result set returns:

id parent_id title
-- --------- --------------
 1      null Animal
 2         1 Mammal
 3         1 Bird
 4         3 Eagle
 5         2 Marsupial
 6         5 Cangaroo

The logic can currently format it as:

+- Animal
   +- Mammal
   |  +- Marsupial
   |     +- Cangaroo
   +- Bird
     +- Eagle

Any SQL syntax could do (hopefully a simple one). Maybe something like:

select *
from (
  format_tree(
    select a as id, b as parent_id, c as title
    from ...
    where ...
  )
) x

For this reporting database I have some freedom to choose the engine, so if there's an idea for another database (Oracle, DB2, SQL Server, MariaDB, etc.) that could also work.

Answer 1

I don't see the point in doing that, but you could pass an array of a pre-defined type:

create type tree_line 
(
  id  integer, 
  parent_id integer, 
  title text
);

Then you can define a function that accepts an array of that type:

create function format_tree(p_data tree_line[])
  returns table (line text)
as
$$
  ...
$$

And call it like this:

select *
from format_tree(array(select (a, b, c)::tree_line
                       from ...
                       where ...));

---

Another option might be to use a JSONB value as the "result set holder":

create function format_tree(p_data jsonb)
  returns table (line text)
as
$$
  ...
$$

Then use it like this:

select *
from format_tree((select jsonb_agg(t)
                  from ( 
                    select a as id, b as parent_id, c as text
                    from ...
                  )  t
                 ));