From: "Chris Faulkner" <chrisf@oramap.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: length of array
Date: Thu, 28 Aug 2003 15:37:55 +0100
Message-ID: <DGENKIKMJILAAKJGFHKFCENICGAA.chrisf@oramap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <3F4D4F71.1030706@joeconway.com>
Importance: Normal

Hello

Thanks for that solution, Joe - nice use of nested functions !

Related to this problem, I want to constrain a selection using elements of
this variable length array. I want to constrain where all elements of the
array are 0.

I would like to do it like this in Oracle

select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0

So if a row only has two elements in the array, but the first two both had
values "0", then the row would return.  At the moment, I have this :

and N.level[1] = 0
and N.level[2] = 0
and N.level[3] = 0
and N.level[4] = 0

but my row with 2 elements in the array won't be returned with this
condition.

Chris

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: 28 August 2003 01:40
To: Chris Faulkner
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] length of array


Chris Faulkner wrote:
> Is there a function in postgres to return the length of an array field ? I
> have seen array_dims(array) but this returns a character value. Ideally,
I'd
> like something numeric returned.
>

Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do
this (for a one-dimensional array at least):

SELECT
   replace(split_part(array_dims(array_fld),':',1),'[','')::int
   as low
FROM tbl;

SELECT
   replace(split_part(array_dims(array_fld),':',2),']','')::int
   as high
FROM tbl;


In 7.4 (now in beta) there are two new functions, array_lower() and
array_upper() that do what you're looking for:

regression=# select array_lower(array_fld, 1) from tbl;
  array_lower
-------------
            1
(1 row)

regression=# select array_upper(array_fld, 1) from tbl;
  array_upper
-------------
            2
(1 row)

See the following links for more on 7.4's array support:
http://developer.postgresql.org/docs/postgres/arrays.html
http://developer.postgresql.org/docs/postgres/functions-array.html
http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTA
X-ARRAY-CONSTRUCTORS

HTH,

Joe