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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
