Обсуждение: 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?
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:
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.
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();
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();
On Mon, Jul 25, 2022 at 10:34 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
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:
From the change to this page I'd say v14:
The release note for v14 say:
PL/PgSQL:
Improve PL/pgSQL's expression and assignment parsing (Tom Lane)
This change allows assignment to array slices and nested record fields.
This change allows assignment to array slices and nested record fields.
David J.
david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
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:
From the change to this page I'd say v14:
https://www.postgresql.org/docs/14/plpgsql-statements.html
The release note for v14 say:«PL/PgSQL:Improve PL/pgSQL's expression and assignment parsing (Tom Lane)This change allows assignment to array slices and nested record fields.»
Thanks for the quick reply, David.
I looked at "43.5.1. Assignment" (https://www.postgresql.org/docs/14/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT).
I searched in the page for « ]. » and found this:
complex_array[n].realpart = 12.3;
The corresponding page for Version 13 has no hits for « ]. ». So that seems to be a clear answer to my question.