Обсуждение: select (fn()).* executes function multiple times

Поиск
Список
Период
Сортировка

select (fn()).* executes function multiple times

От
Andrey
Дата:
PostgreSQL 10.5, 11.1:

create table fn_ret_test_storage (f1 int default 1, f2 int default 2, f3 int default 3);

create or replace function fn_ret_test(out f1 int, out f2 int, out f3 int)
returns record
language plpgsql
as
$$
begin
insert into fn_ret_test_storage default values
returning * into f1, f2, f3;
end
$$;

do
$$
declare
_count int;
begin
select count(*) into _count from fn_ret_test_storage;
perform (fn_ret_test()).*;
select count(*) - _count into _count from fn_ret_test_storage;
raise notice '----------------- % rows inserted -----------------', _count;
end
$$;

-- output: NOTICE:  ----------------- 3 rows inserted -----------------

Re: select (fn()).* executes function multiple times

От
"David G. Johnston"
Дата:
On Fri, Nov 16, 2018 at 7:49 AM Andrey <parihaaraka@gmail.com> wrote:
> perform (fn_ret_test()).*;

Yes it does [execute fn_ret_rest multiple times]; its known behavior
that while surprising is unlikely to get fixed.  Its simple to work
around using the LATERAL construct (i.e., placing said function call
in the FROM clause).

The root problem is the use of ".*" - the rewriter turns it into:
SELECT fn_ret_test.f1(), fn_ret_test().f2, fn_ret_test().f3; which
when written this way become evident why it is executed multiple
times.

David J.

Re: select (fn()).* executes function multiple times

От
Tom Lane
Дата:
That's not a bug, that's just how it works.

You can get the other way with something like

SELECT f.* FROM ..., LATERAL fn(...) f;

            regards, tom lane

Re: select (fn()).* executes function multiple times

От
Andrey
Дата:
O_o
I got it. Sorry for disturbing you.
Thanks

regards, Andrey L

пт, 16 нояб. 2018 г. в 17:57, Tom Lane <tgl@sss.pgh.pa.us>:
That's not a bug, that's just how it works.

You can get the other way with something like

SELECT f.* FROM ..., LATERAL fn(...) f;

                        regards, tom lane