Re: array_dims array_lower/upper distance - Mailing list pgsql-general

From Guy Fraser
Subject Re: array_dims array_lower/upper distance
Date
Msg-id 1127488154.4437.35.camel@sigurd.incentre.net
Whole thread Raw
In response to Re: array_dims array_lower/upper distance  (Bruno Wolff III <bruno@wolff.to>)
Responses Re: array_dims array_lower/upper distance
List pgsql-general
On Thu, 2005-22-09 at 21:52 -0500, Bruno Wolff III wrote:
> On Thu, Sep 22, 2005 at 14:16:48 -0600,
>   Guy Fraser <guy@incentre.net> wrote:
> > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote:
> > > Guy Fraser <guy@incentre.net> writes:
> > >
> > > > So to answer his question he would likely want :
> > > >
> > > > SELECT
> > > >  array_upper(item,1) - array_upper(item,0) + 1 as elements
> > > > FROM
> > > >  arraytest ;
> > >
> > > Note that this doesn't work for empty arrays.
> > > It will return NULL instead of 0.
> > Your response was not at all helpfull, I would like to
> > encourage you to expand on what I put off the top of my
> > head.
> >
> > I have not used array_upper() before, and the question was
> > how to return the total number of elements, not how to
> > handle NULL and empty arrays.
>
> I think his point was that your example was going to give the wrong answer
> for empty arrays, which is relevant to your question. The normal way around
> that is to use the COALESCE function.
OK what I jotted down was totally wrong.

This is slightly more correct :

SELECT
 array_upper(item,1) - array_lower(item,1) + 1 as elements
FROM
 arraytest ;

Without do a tonne of research, I can not refine this to handle
all circumstances.

Can someone point me to documentation that explains the function
better than :

Dimensions can also be retrieved with array_upper and array_lower, which
return the upper and lower bound of a specified array dimension,
respectively.

The table "Table 9-36. array Functions" does not explain how empty
and null arrays are handled either.

How do array_upper() and array_lower() respond to :
1) NULL
2) Empty Array
3) Nonexistent requested dimension

Also is there a function that specifies how many dimensions the
array has?



pgsql-general by date:

Previous
From: Yonatan Ben-Nes
Date:
Subject: Re: How many insert + update should one transaction handle?
Next
From: John Seberg
Date:
Subject: Re: COPY - permission denied