From: | Ioana Danes <ioanasoftware(at)yahoo(dot)ca> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Function use in query |
Date: | 2013-06-05 18:31:18 |
Message-ID: | 1370457078.82284.YahooMailNeo@web164602.mail.gq1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Ramsey | 2013-06-05 18:31:57 | Re: What's a good way to improve this query? |
Previous Message | BladeOfLight16 | 2013-06-05 18:28:36 | point_ops with GiST PostGIS Spatial Index |