Hello Dear List,
this is a re-post with a more clear title and rewrite.
I have a design issue :
My function works on ordered set of rows and returns ordered set of rows (less thant input). I want to use it in standard sql query (in WITH for example).
What is best practice for input?
I see 3 solutions :
_give table name as input || so no order unless I use view, doesn't work with CTE and all.
_give array and use unnest/arrayagg || bad performance with big input
_give refcursor || non-convenient because of transaction and require 2 separate queries.
Is there another way?
I would like to use it in a single sql statement, which leaves only array, which will perform bad (I may have hundreds of k of rows as input).
Also the function need all the row in input to work, and can't operate row by row.
It would be like an aggregate, but returning several rows.
Of course I read all the doc I could find, but doc doesn't give best practice !
Thank you very much for helping, I am in a stalemate now, and can't progress further.
Below is the original message, giving details over what the function do.
Cheers,
Rémi-C
I wrote a plpgsql function to compute union of time range that works :
[1,4]U[3,8]U[12,14]U[16,18] ---> [1,8]U[12,14]U[16,18]
It works on multiple rows.
My issue is a design issue :
I want to work on set of row and return set of row.
I am aware I could take as input/output array of range but I don't want
(memory/casting cost).
Currently the function takes a cursor on a table and output a setof record.
I would like that the function can blend in multiple subqueries smoothly, as
WITH (
first query to get range),
(query computing union
),
(query using computed union
) etc.
Currently I have to execute 2 sql statment :
create cursor on ranges;
WITH (function to compute union)
,
(query...)
The only kind of function taking set of record as input I know of is
aggregate function, but it returns only one row and the output of union can
take multiple row.
Any insight would be greatly appreciated.
Cheers,
Rémi-C
