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
