select (fn()).* executes function multiple times
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 -----------------
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.
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
O_o
I got it. Sorry for disturbing you.
Thanks
regards, Andrey L
пт, 16 нояб. 2018 г. в 17:57, Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
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