I copied my testcase at the end. It runs OK and produces the output that I
expect using PG Version 14.4. But using Version 11.9 (and earlier 11
sub-versions), it fails to compile with this error:
syntax error at or near "."
lhs[j].a := rhs[j].a;
^
If I comment out the "Ideal approach" loop, then it runs fine in PG 11 and
produces the same output as the "Ideal approach" does in PG 14,
I looked at the Release Notes for each of Versions 14, 13, and 12:
https://www.postgresql.org/docs/12/release-12-1.html
<https://www.postgresql.org/docs/12/release-12-1.html>
https://www.postgresql.org/docs/13/release-13-1.html
<https://www.postgresql.org/docs/13/release-13-1.html>
https://www.postgresql.org/docs/14/release-14-1.html
<https://www.postgresql.org/docs/14/release-14-1.html>
I searched in the page for "array". But there were no relevant hits on any of
those three pages.
Can anybody tell me which PG release brought the improvement?
p.s., I'm asking because YugabyteDB still uses the PG 11.2 SQL processing code.
I'm promised that fairly soon a new YugabyteDB version will use the PG 13 SQL
processing code. I'm hoping that, then, I'll be able to retire my workaround.
--------------------------------------------------------------------------------
create type t as (a text, b text);
create function f()
returns table(z text)
language plpgsql
as $body$
declare
lhs t[] not null := array[('??', '??'), ('??', '??'), ('??', '??')];
rhs constant t[] not null := array[('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')];
r t not null := ('??', '??');
begin
for j in 1..3 loop
z := rhs[j].a||', '||rhs[j].b; return next;
end loop;
-- Workaround
for j in 1..3 loop
r := rhs[j];
r.a := rhs[j].a;
r.b := rhs[j].b;
lhs[j] := r;
end loop;
-- Ideal approach.
for j in 1..3 loop
lhs[j].a := rhs[j].a;
lhs[j].b := rhs[j].b;
end loop;
z := ''; return next;
for j in 1..3 loop
z := lhs[j].a||', '||lhs[j].b; return next;
end loop;
end;
$body$;
select z from f();
