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...
Butin my reporting tool I can only use views or tables so I thought about creating a view on top of a query from a
tablejoined with a store procedure... One of the table columns will be a filter for the procedure. There is a problem
withthis approach as the procedure is executed for each returned column and that is a performance killer. Is there any
similarsyntax 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.CashdrawerIDin (1);
select * from tmp_log;
The tmp_log shows how many time the procedure executes.
Thank you,
I
