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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
