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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: fatwildcat(at)gmail(dot)com
Cc: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Re: pl/pgsql errors when multi-dimensional arrays are used
Date: 2021-04-29 14:31:43
Message-ID: 2987320.1619706703@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

PG Doc comments form <noreply(at)postgresql(dot)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

In response to

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2021-04-29 17:32:59 Re: Foreign Keys being able to reference same table not spelled out in documentation
Previous Message PG Doc comments form 2021-04-29 12:07:38 Foreign Keys being able to reference same table not spelled out in documentation