How to pass a set or unnamed relation into a PostgreSQL function?

Question

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?

Answer 1

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 ....