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
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index bb4657e..dc7b6f4 100644
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -224,7 +224,9 @@ SELECT name FROM sal_emp WHERE pay_by_quarter[1] <>
pay_by_quarter[2];
By default <productname>PostgreSQL</productname> uses a
one-based numbering convention for arrays, that is,
an array of <replaceable>n</> elements starts with
<literal>array[1]</literal> and
- ends with <literal>array[<replaceable>n</>]</literal>.
+ ends with <literal>array[<replaceable>n</>]</literal>. Negative
+ array subscript numbers indicate that the position of the element is
calculated from
+ the end of the array, with -1 indicating the last element in the array.
</para>
<para>
@@ -242,6 +244,34 @@ SELECT pay_by_quarter[3] FROM sal_emp;
</para>
<para>
+ This query retrieves the last quarter pay of all employees:
+
+<programlisting>
+SELECT pay_by_quarter[-1] FROM sal_emp;
+
+ pay_by_quarter
+----------------
+ 10000
+ 25000
+(2 rows)
+</programlisting>
+</para>
+
+<para>
+ This query retrieves the pay of all employees for the last three quarters:
+<programlisting>
+SELECT pay_by_quarter[-3:4] FROM sal_emp;
+
+ pay_by_quarter
+---------------------
+ {10000,10000,10000}
+ {25000,25000,25000}
+(2 rows)
+
+</programlisting>
+ </para>
+
+ <para>
We can also access arbitrary rectangular slices of an array, or
subarrays. An array slice is denoted by writing
<literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
diff --git a/src/backend/utils/adt/arrayfuncs.c
b/src/backend/utils/adt/arrayfuncs.c
index fb4cbce..9d6c3f1 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -1786,6 +1786,8 @@ array_ref(ArrayType *array,
}
for (i = 0; i < ndim; i++)
{
+ if (indx[i] < 0) /* A negative index number indicates a
position calculated from the end of the array */
+ indx[i] = dim[i] + indx[i] + lb[i];
if (indx[i] < lb[i] || indx[i] >= (dim[i] + lb[i]))
{
*isNull = true;
@@ -1914,6 +1916,10 @@ array_get_slice(ArrayType *array,
for (i = 0; i < nSubscripts; i++)
{
+ if (lowerIndx[i] < 0) /* A negative index number indicates a
position calculated from the end of the array */
+ lowerIndx[i] = dim[i] + lowerIndx[i] + lb[i];
+ if (upperIndx[i] < 0) /* A negative index number indicates a
position calculated from the end of the array */
+ upperIndx[i] = dim[i] + upperIndx[i] + lb[i];
if (lowerIndx[i] < lb[i])
lowerIndx[i] = lb[i];
if (upperIndx[i] >= (dim[i] + lb[i]))
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
