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',

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?


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

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 ....
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.