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

Support for negative index values in array fetching

From: "Valtonen, Hannu" <hannu(dot)valtonen(at)hut(dot)fi>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Support for negative index values in array fetching
Date: 2011-01-02 10:45:29
Message-ID: 4D205749.6040704@hut.fi (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

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.

i.e.

postgres=# CREATE TABLE blah (a int[]);
CREATE TABLE
Time: 11.357 ms
postgres=# INSERT INTO blah (a) VALUES (ARRAY[1,2,3,4,5,6,7,8,9,10]);
INSERT 0 1
Time: 1.282 ms
postgres=# SELECT a[-1] FROM blah;
  a
----
  10
(1 row)

Time: 0.450 ms
postgres=# SELECT a[-5:10] FROM blah;
       a
--------------
  {6,7,8,9,10}
(1 row)

Time: 0.949 ms

While testing this I BTW ran into funny behaviour in setting array 
slices, as in:

postgres=# update blah set a[-5] = 12;
UPDATE 1
Time: 1.500 ms
postgres=# select * from blah;
                              a
------------------------------------------------------------
  [-5:10]={12,NULL,NULL,NULL,NULL,NULL,1,2,3,4,5,6,7,8,9,10}
(1 row)

Time: 0.431 ms

And since this negative array expansion behaviour totally surprised me, 
I haven't changed that in this patch at all.

-- 
Hannu Valtonen



Attachment: array.diff
Description: text/plain (2.6 KB)

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2011-01-02 10:57:02
Subject: Re: Extension upgrade, patch v0: debug help needed
Previous:From: Simon RiggsDate: 2011-01-02 10:39:51
Subject: Re: Sync Rep Design

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