Re: Support for negative index values in array fetching

On Jan2, 2011, at 11:45 , Valtonen, Hannu wrote:
> I ran into the problem of getting the last n elements out of an array and while some workarounds do exist:
> (http://stackoverflow.com/questions/2949881/getting-the-last-element-of-a-postgres-array-declaratively) I was still annoyed that I couldn't just ask for the last n values in an array Python/Perl style.
>
> 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). Instead, you can each dimension's lower and upper bound for index values
with array_lower() and array_upper().

Here's an example

fgp=> do $$
declare a text[];
begin
a[-1] := 'foo';
a[0] := 'bar';
raise notice 'a[-1] == %', a[-1];
end
$$ language 'plpgsql' ;

This will raise the notice 'a[-1] == foo'!

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...

best regards,
Florian Pflug