Обсуждение: 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 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
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