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.

Why pass a "result"? Why not just create a function that queries the table and returns the "formatted output"? — user330315
– user330315, Commented Jan 20, 2021 at 20:56
@a_horse_with_no_name Thanks for the comment. The thing is the "parameter data" is assembled on the fly and can be any query that produces a tree. I wanted to somehow store the format_tree() logic in the database itself as a procedure/function if possible. — The Impaler
– The Impaler, Commented Jan 20, 2021 at 21:05

score 1 · Accepted Answer · 2021-01-20 21:27:27Z

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
                 ));

edited Jan 20, 2021 at 21:27

answered Jan 20, 2021 at 21:11

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

The Impaler Over a year ago

I was just thinking about this. Excellent idea. Will try it out.

user330315 Over a year ago

@TheImpaler: another option would be to collect the result into a JSONB array. A bit more "dynamic" and flexible, but doesn't need the tree_line type.

Collectives™ on Stack Overflow

Result Set parameter to a PostgreSQL function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related