PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?
Date: 2022-07-25 17:34:31
Message-ID: 0ABA97CF-ADA4-43D5-A2DB-38A705561494@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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();

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-07-25 17:43:58 Re: PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?
Previous Message Adrian Klaver 2022-07-25 17:19:32 Re: Password reset link / 'less' does not exit in psql version 13.4