Postgresql – How to pass a set or unnamed relation into a PostgreSQL function

postgresql

In PostgreSQL, there are Set-Returning-Functions (SRF). But I'm wondering if there is also a way to pass an unknown relation into a function? In other words, is it possible to write Set-Taking-Functions (so to speak), other than passing the name of the tuple-set?

Obviously, there is dynamic SQL where one can pass a table name. With pgRouting, one can even use an entire string to pass the set of tuples into the function:

SELECT * FROM shortest_path('
   SELECT gid AS id, 
          start_id::int4 AS source, 
          end_id::int4 AS target, 
          shape_leng::float8 AS cost
   FROM network',
1,
5110,
false,
false);

But the SQL string in the parameter does not seem very secure. The other possibility is to store the result into a temporary table first and pass the name of the temporary table, which seems to be against the idea of set-based programming in SQL.

Are there any alternative ways of passing a set into a function?

Best Answer

You could define a record type that represents one tuple of what you want to pass, then pass an array of that type:

create type path_info
(
  id  integer, 
  source integer,
  target integer, 
  cost float
);

Then pass an array to your function:

select *
from shortest_path((select array_agg( (gid,start_id,end_id,shape_leng)::path_info ) 
                    from network), 
                   ...);

The function parameter needs to be defined as path_info[]: 

create function shortest_path(p_path_info path_info[], ...)
  returns ...
as
...

Depending on what you are doing with those values, a function that receives a single record used with a lateral join might be more efficient:

Something like:

select *
from network n
  join lateral shortest_path( (n.gid, n.start_id, n.end_id, n.shape_leng)::path_info, ...) on ....
Related Question