Message-ID: <3F4D4F71.1030706@joeconway.com>
Date: Wed, 27 Aug 2003 17:40:17 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Chris Faulkner <chrisf@oramap.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: length of array
References: <DGENKIKMJILAAKJGFHKFCEMNCGAA.chrisf@oramap.com>
In-Reply-To: <DGENKIKMJILAAKJGFHKFCEMNCGAA.chrisf@oramap.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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-SYNTAX-ARRAY-CONSTRUCTORS

HTH,

Joe