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?