Обсуждение: pl/pgsql errors when multi-dimensional arrays are used
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
try:
a.fld1[1] = '[221,222,223,224]';
чт, 29 апр. 2021 г. в 10:35, PG Doc comments form <noreply@postgresql.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
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@postgresql.org <mailto:noreply@postgresql.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 >
Hi
čt 29. 4. 2021 v 12:33 odesílatel Rafal Dabrowa <fatwildcat@gmail.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@postgresql.org <mailto:noreply@postgresql.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}}
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
PG Doc comments form <noreply@postgresql.org> writes: > I have PostgreSQL 13. Let's declare the type below, then use it in > pl/pgsql: > create type typ1 as ( > fld1 int[][] > ); I think you have a fundamental misunderstanding of how multidimensional arrays work in Postgres. There's no separate type for 2-D vs 1-D arrays; that is, the extra pair of brackets you wrote above is just noise. What matters is what you put into the array at runtime, and the syntax you use to do it. > a.fld1 = '{{121,122,123,124}}'; -- OK (1) Fine, you stored a 2-D array into fld1. > a.fld1[1] = '{221,222,223,224}'; -- fails (2) This fails on semantic grounds because a non-slice assignment or fetch of an int array element must store or retrieve an int. You're trying to store an array slice, which requires that you use [m:n] subscript notation. It'd be correct to write either of a.fld1[1:4] = '{221,222,223,224}'; a.fld1[1:] = '{221,222,223,224}'; which unfortunately plpgsql doesn't support in released versions (that's fixed for v14 though). regards, tom lane