Thread: Function use in query
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
Ioana Danes wrote > Hi All, > Is there any similar syntax that only invokes the procedure once and > returns all the columns? Generic, adapt to fit your needs. WITH func_call AS ( SELECT function_call(...) AS func_out_col ) SELECT (func_out_col).* FROM func_call; Basically you have to execute the function call and leave the result as a single column (a row type). Then, in another layer of the query, you expand that single column into its components using "*". Because you are expanding a column and not a table you must put the column name in "()" - otherwise the parser thinks "func_out_col" is a table and errors out. This all definitely applies to 9.2 and earlier. 9.3 (with lateral) may behave differently... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Function-use-in-query-tp5758051p5758066.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Ioana Danes wrote > Hi All, > Is there any similar syntax that only invokes the procedure once and > returns all the columns? Generic, adapt to fit your needs. WITH func_call AS ( SELECT function_call(...) AS func_out_col ) SELECT (func_out_col).* FROM func_call; Basically you have to execute the function call and leave the result as a single column (a row type). Then, in another layer of the query, you expand that single column into its components using "*". Because you are expanding a column and not a table you must put the column name in "()" - otherwise the parser thinks "func_out_col" is a table and errors out. This all definitely applies to 9.2 and earlier. 9.3 (with lateral) may behave differently... David J. Hi David, Thank you for your reply, I haven't thought about it. This works as expected if I don't need to filter the table tmp_Cashdrawer: select tmp_Cashdrawer.CashdrawerID, (test1(tmp_Cashdrawer.CashdrawerID)).* from tmp_Cashdrawer where tmp_Cashdrawer.CashdrawerID in (1); If I will have to filter the tmp_Cashdrawer table then it executes the function for the all the cash drawers and then filterout the result which again is not efficient... I might use an aggregate table for this. This way I can use a simple function call to update the aggregate table when a cashdrawer is balanced or before executing the report. Thanks again for your reply, Ioana
Ioana Danes wrote > > If I will have to filter the tmp_Cashdrawer table then it executes the > function for the all the cash drawers and then filter out the result which > again is not efficient... Hm???? SELECT function_call(...) FROM tbl WHERE tbl.pk = ...; That should only cause function_call to execute a single time. If it is not I'd suggest providing the actual query as well as the "EXPLAIN" plan for it. It is possible that making the function "cost more" might be necessary but doubtful. You can force the table filter to be executed first by using a simple sub-select: SELECT function_call(...) FROM (SELECT * FROM tbl WHERE tbl.pk = ...) filtered_tbl; Though it is possible the planner would re-arrange this to the simple form and still cause a problem. Explain is your friend. Combine that with the "WITH" if you need to expand the results of function_call without causing it to execute multiple times - once for each column being expanded. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Function-use-in-query-tp5758051p5758159.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Ioana Danes wrote
>
> If I will have to filter the tmp_Cashdrawer table then it executes the
> function for the all the cash drawers and then filter out the result which
> again is not efficient...
Hm????
SELECT function_call(...)
FROM tbl
WHERE tbl.pk = ...;
That should only cause function_call to execute a single time. If it is not
I'd suggest providing the actual query as well as the "EXPLAIN" plan for it.
It is possible that making the function "cost more" might be necessary but
doubtful.
You can force the table filter to be executed first by using a simple
sub-select:
SELECT function_call(...)
FROM (SELECT * FROM tbl WHERE tbl.pk = ...) filtered_tbl;
Though it is possible the planner would re-arrange this to the simple form
and still cause a problem. Explain is your friend.
Combine that with the "WITH" if you need to expand the results of
function_call without causing it to execute multiple times - once for each
column being expanded.
David J.
Hi David,
Here is the simplified example:
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$;
create view tmp_view as
with func as (
select tmp_Cashdrawer.CashdrawerID, test1(tmp_Cashdrawer.CashdrawerID) as call
from tmp_Cashdrawer
)
select func.CashdrawerID, (func.call).*
from func;
delete from tmp_log;
select * from tmp_view
where CashdrawerID in (1);
select * from tmp_log;
Ioana
Ioana Danes wrote > create view tmp_view as > with func as ( > select tmp_Cashdrawer.CashdrawerID, test1(tmp_Cashdrawer.CashdrawerID) > as call > from tmp_Cashdrawer > ) > select func.CashdrawerID, (func.call).* > from func; So yeah, putting this into a view will not work. The WITH/CTE construct is an optimization barrier so the WHERE clause that is going to be used cannot be applied before the function call is performed - only afterwards. As I said before LATERAL in 9.3 may help - though others will have to comment - in cleaning up the syntax and multiple-calls. For <= 9.2 you will have to either live with the slightly verbose syntax or wrap what you need into user-defined functions. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Function-use-in-query-tp5758051p5758201.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.