Re: array_dims array_lower/upper distance

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(at)incentre(dot)net> wrote:
> > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote:
> > > Guy Fraser <guy(at)incentre(dot)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?