Function use in query

[Ioana Danes <ioanasoftware@xxxxxxxx>]

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