Ioana Danes wrote
> Hi All, > Is there any similar syntax that only invokes the procedure once and > returns all the columns? Generic, adapt to fit your needs. WITH func_call AS ( SELECT function_call(...) AS func_out_col ) SELECT (func_out_col).* FROM func_call; Basically you have to execute the function call and leave the result as a single column (a row type). Then, in another layer of the query, you expand that single column into its components using "*". Because you are expanding a column and not a table you must put the column name in "()" - otherwise the parser thinks "func_out_col" is a table and errors out. This all definitely applies to 9.2 and earlier. 9.3 (with lateral) may behave differently... David J. Hi David, Thank you for your reply, I haven't thought about it. This works as expected if I don't need to filter the table tmp_Cashdrawer: select tmp_Cashdrawer.CashdrawerID, (test1(tmp_Cashdrawer.CashdrawerID)).* from tmp_Cashdrawer where tmp_Cashdrawer.CashdrawerID in (1); If I will have to filter the tmp_Cashdrawer table then it executes the function for the all the cash drawers and then filter out the result which again is not efficient... I might use an aggregate table for this. This way I can use a simple function call to update the aggregate table when a cash drawer is balanced or before executing the report. Thanks again for your reply, Ioana -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
