Re: Function use in query

Поиск
Список
Период
Сортировка
От Ioana Danes
Тема Re: Function use in query
Дата
Msg-id 1370542912.58957.YahooMailNeo@web164601.mail.gq1.yahoo.com
обсуждение исходный текст
Ответ на Re: Function use in query  (David Johnston <polobo@yahoo.com>)
Ответы Re: Function use in query  (David Johnston <polobo@yahoo.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Load Mysql table CSV into postgresql
Следующее
От: bricklen
Дата:
Сообщение: Re: Database performs massive reads when I'm doing writes.