Hi All,
I would like to ask for some suggestions regarding the following scenario. I have a cash drawer table and for each cash drawer I have a function that collects and transforms data from different tables (and a web service using www_fdw). In normal scenarios I would have a function to return the data and voila... But in my reporting tool I can only use views or tables so I thought about creating a view on top of a query from a table joined with a store procedure... One of the table columns will be a filter for the procedure. There is a problem with this approach as the procedure is executed for each returned column and that is a performance killer. Is there any similar syntax that only invokes the procedure once and returns all the columns? Any suggestions are greatly appeciated. Here is the simplified schema: drop table if exists tmp_Cashdrawer; create table tmp_Cashdrawer (CashdrawerID integer); insert into tmp_Cashdrawer values (1),(2),(3),(4),(5); drop table if exists tmp_log; create table tmp_log (txlog text); drop function if exists test1(IN iCashdrawerID INTEGER); CREATE OR REPLACE FUNCTION test1(IN iCashdrawerID INTEGER) RETURNS TABLE ( value1 integer, value2 integer) LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER AS $BODY$ BEGIN insert into tmp_log VALUES ('CashDrawerid: '||iCashdrawerID); RETURN QUERY select 1 as value1, 1 as value2 ; END; $BODY$; delete from tmp_log; select tmp_Cashdrawer.CashdrawerID, (test1(tmp_Cashdrawer.CashdrawerID)).* from tmp_Cashdrawer where tmp_Cashdrawer.CashdrawerID in (1); select * from tmp_log; The tmp_log shows how many time the procedure executes. Thank you, I -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general