Re: pl/pgsql errors when multi-dimensional arrays are used

Hi

čt 29. 4. 2021 v 12:33 odesílatel Rafal Dabrowa <fatwildcat(at)gmail(dot)com>
napsal:

> This also returns ERROR: invalid input syntax for type integer:
> "[221,222,223,224]"
>
> On 4/29/2021 9:48 AM, KraSer wrote:
> > try:
> > a.fld1[1] = '[221,222,223,224]';
> >
> > чт, 29 апр. 2021 г. в 10:35, PG Doc comments form
> > <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org>>:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/arrays.html
> > Description:
> >
> > I have PostgreSQL 13. Let's declare the type below, then use it in
> > pl/pgsql:
> >
> > create type typ1 as (
> > fld1 int[][]
> > );
> >
> > do $$
> > declare
> > a typ1;
> > begin
> > a.fld1 = '{{121,122,123,124}}'; -- OK
> > (1)
> > a.fld1[1] = '{221,222,223,224}'; -- fails
> > (2)
> > a.fld1[1][1] = 321; -- OK
> > (3)
> > a.fld1[1][2] = 322; -- OK unless line (1)
> > is removed
> > end;
> > $$;
> >
> > In line (2) the plql reports ERROR: invalid input syntax for type
> > integer:
> > "{221,222,223,224}"
> > When lines (1) and (2) are removed, psql reports ERROR: array
> > subscript out
> > of range
> >
> > Is this expected behavior? Why?
> >
> > Rafal
> >
>

Postgres's arrays don't allow any modification that creates some gap in
the array. Next - Postgres's arrays are multidimensional arrays, and these
arrays are not an arrays or arrays.

So your line (1) cannot work.

You can write

a.fld1[1][1:4] := '{221,222,223,224}';

Case (3) fails, because this operation on empty array creates gap on
position 1,1.

you can use an function array_fill

DO
postgres=# do $$
declare a int[];
begin
a := array_fill(null::int, array[2,2]);
a[1][2] := 322;

raise notice 'a=%', a;
end;
$$;
NOTICE: a={{NULL,322},{NULL,NULL}}

Regards

Pavel