Skip site navigation (1) Skip section navigation (2)

Re: Support for negative index values in array fetching

From: Florian Pflug <fgp(at)phlo(dot)org>
To: "Valtonen, Hannu" <hannu(dot)valtonen(at)hut(dot)fi>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Support for negative index values in array fetching
Date: 2011-01-02 11:47:10
Message-ID: A343FC93-9677-47C0-AF08-561C3BCD8AA9@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-01-02 12:26:43
Subject: Re: [COMMITTERS] pgsql: Basic foreign table support.
Previous:From: Dimitri FontaineDate: 2011-01-02 11:43:13
Subject: Re: Extension upgrade, patch v0: debug help needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group