Re: Support for negative index values in array fetching

On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote:
> > Here's a patch to add support for negative index values in fetching elements from an array.
>
> That won't work. In SQL, array indices don't necessarily start with 0 (or 1, or *any*
> single value).

FYI, this is true for PostgreSQL, but not in SQL in general. In the
standard, array indexes go from 1 to N.

> The only way around that would be to introduce magic constants "lower", "upper" that
> can be used within index expressions and evaluate to the indexed dimension's lower
> and upper bound. You'd then use
>
> my_array[upper], my_array[upper-1], ...
>
> to refer to the last, second-to-last, ... element in the array. Actually doing this
> could get pretty messy, though - not sure if it's really worth the effort...

How about just some functions:

array_first(array, dim)
array_last(array, dim)

Perhaps some variants for splice vs. scalar.