remi.cura wrote
> What is best practice for input?
There is none; you have options because different scenarios require
different solutions.
> 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.
To generalize:
Input Types:
-indirect (refcursor or table name)
-direct (parameters w/ data - including arrays)
Output Types:
-Scalar
-Set-Of
-Indirect (refcursor, table name)
Note that you can standardize on a table name and use the indirect output to
communicate other information - like what subset of the table do you want to
consider.
One thought is to populate an input staging table using some kind of
"transaction id"; run a function providing it the "transaction id" upon
which it should operate; have said function populate an output table using
the same transaction id. In effect you cache both the input and output data
and then have your application query those caches (mainly the output cache)
to obtain its results.
Much more knowledge of the architecture in which the problem needs to
operate, and the problem itself, is needed to make reasonable suggestions
(as opposed to mere thought starters).
I find the statement "non-convenient because of transaction and require 2
separate queries" to be utter nonsense at face value but again that stems
from not knowing what limitations you are facing.
You can embed "order" information into a table and I am unsure why it would
not work with a CTE. I imagine something like:
<not tested for syntax but the idea holds and is possible>
WITH pop ( SELECT populate_table() AS trans_id )
, SELECT * FROM process_table ( SELECT trans_id FROM pop )
;
HTH
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/best-practice-for-set-of-rows-function-set-of-rows-tp5771189p5771265.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
