Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Andrey
Date:
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

From
"David G. Johnston"
Date:
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

From
Tom Lane
Date:
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

From
Andrey
Date:
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