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... 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 filter out 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 cash drawer is balanced or before executing the report.
Thanks again for your reply,
Ioana
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general