Support for negative index values in array fetching

[Valtonen, Hannu]

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.

[Florian Pflug]

(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

[Peter Eisentraut]

Post by Florian Pflug

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.

Post by Florian Pflug
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.

[Pavel Stehule]

Hello

Post by Florian Pflug

Here's a patch to add support for negative index values in fetching elements from an array.

negative arguments for array can be really strange

Post by Florian Pflug
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.

Post by Florian Pflug
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...

array_first(array, dim)
array_last(array, dim)
Perhaps some variants for splice vs. scalar.

Itakagi has a function trim_array in
http://archives.postgresql.org/message-id/AANLkTinrRubdSSWvqO481sL0EyGz830=***@mail.gmail.com
patch. It's similar to array_first.

I understand to a missing functionality for FIFO or LIFO queues
implementation based on array. There can be function that reduce a
array to first or last n items, and functions that returns first or
last items.

some like array_first(array, items), array_last(array, items),
array_remove_first(array, items), array_remove_last(array, items)

or some similar

Pavel

--
http://www.postgresql.org/mailpref/pgsql-hackers

[Florian Pflug]

Post by Peter Eisentraut

Post by Florian Pflug
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...

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

You image these to return the actual element, not the first and last index value, right?
Because we already have array_lower() and array_upper() which return the lower and upper
index bound for a certain dimension.
(http://www.postgresql.org/docs/9.0/interactive/functions-array.htm)

A more general solution would be a function

array_relative(array anyarray, indices int[])

which would return the element indexed by <indices>, where positive indices are assumed to
be relative to the respective dimension's lower bound and negative indices to the
upper bound + 1.

For slices, we could additionally have

array_relative(array anyarray, indices_start int[], indices_end int[])

best regards,
Florian Pflug

[Pavel Stehule]

Post by Florian Pflug

Post by Peter Eisentraut

Post by Florian Pflug
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...

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

You image these to return the actual element, not the first and last index value, right?
Because we already have array_lower() and array_upper() which return the lower and upper
index bound for a certain dimension.
(http://www.postgresql.org/docs/9.0/interactive/functions-array.htm)
A more general solution would be a function
array_relative(array anyarray, indices int[])

I don't think so this design helps. instead maintaining a data array,
you should to maintain a indices array.

Pavel

Post by Florian Pflug
which would return the element indexed by <indices>, where positive indices are assumed to
be relative to the respective dimension's lower bound and negative indices to the
upper bound + 1.
For slices, we could additionally have
array_relative(array anyarray, indices_start int[], indices_end int[])
best regards,
Florian Pflug
--
http://www.postgresql.org/mailpref/pgsql-hackers

[Florian Pflug]

Post by Pavel Stehule

Post by Florian Pflug

Post by Peter Eisentraut

Post by Florian Pflug
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...

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

You image these to return the actual element, not the first and last index value, right?
Because we already have array_lower() and array_upper() which return the lower and upper
index bound for a certain dimension.
(http://www.postgresql.org/docs/9.0/interactive/functions-array.htm)
A more general solution would be a function
array_relative(array anyarray, indices int[])

I don't think so this design helps. instead maintaining a data array,
you should to maintain a indices array.

How so? You'd still be able to get the last element by simply writing

array_relative(some_array, array[-1]).

Or, if we made the function variadic, by writing

array_relative(some_array, -1).

It's essentially what the OP proposed, but with the function array_relative() in place of
the indexing operator [].

best regards,
Florian Pflug

[Pavel Stehule]

Post by Florian Pflug

Post by Pavel Stehule

Post by Florian Pflug

Post by Peter Eisentraut

Post by Florian Pflug
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...

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

You image these to return the actual element, not the first and last index value, right?
Because we already have array_lower() and array_upper() which return the lower and upper
index bound for a certain dimension.
(http://www.postgresql.org/docs/9.0/interactive/functions-array.htm)
A more general solution would be a function
array_relative(array anyarray, indices int[])

I don't think so this design helps. instead maintaining a data array,
you should to maintain a indices array.

How so? You'd still be able to get the last element by simply writing
 array_relative(some_array, array[-1]).
Or, if we made the function variadic, by writing
 array_relative(some_array, -1).

Sorry, but It isn't too intuitive. Minimally for me. Why you don't
thinking about simple functions with only positive arguments. There
are only four combinations. I don't think we must have only one super
function.

we need functionality for:

a) get first n items
b) get items without last n items
c) get last n items
d) skip first n items

I think so this functionality is relative important, so we can use a
richer api.

Maybe we thinking about different use cases.

Pavel

Post by Florian Pflug
It's essentially what the OP proposed, but with the function array_relative() in place of
the indexing operator [].
best regards,
Florian Pflug

[Florian Pflug]

Post by Pavel Stehule

Post by Florian Pflug
How so? You'd still be able to get the last element by simply writing
array_relative(some_array, array[-1]).
Or, if we made the function variadic, by writing
array_relative(some_array, -1).

Sorry, but It isn't too intuitive. Minimally for me. Why you don't
thinking about simple functions with only positive arguments. There
are only four combinations. I don't think we must have only one super
function.
a) get first n items
b) get items without last n items
c) get last n items
d) skip first n items

Now you've moved the goalpost - the OP wanted to access individual
elements, not slices! To support slices, a three-argument version
of array_relative() would be required, with the signature

array_relative(some_array anyarray, first int[], last int[])

Your requirements (a) to (d) are then easily satisfied

a) array_relative(ary, array[0], array[n-1])
b) array_relative(ary, array[0], array[-n-1])
c) array_relative(ary, array[-n], array[-1])
d) array_relative(ary, array[n], array[-1])

The individual function approach might be a tad more readable for
one-dimensional arrays, but they don't scale well to the general
case.

Maybe the OP could comment on whether any of these solutions
would fit his needs?

best regards,
Florian Pflug

[Pavel Stehule]

Post by Florian Pflug

Post by Pavel Stehule

Post by Florian Pflug
How so? You'd still be able to get the last element by simply writing
 array_relative(some_array, array[-1]).
Or, if we made the function variadic, by writing
 array_relative(some_array, -1).

Sorry, but It isn't too intuitive. Minimally for me. Why you don't
thinking about simple functions with only positive arguments. There
are only four combinations. I don't think we must have only one super
function.
a) get first n items
b) get items without last n items
c) get last n items
d) skip first n items

Now you've moved the goalpost - the OP wanted to access individual
elements, not slices! To support slices, a three-argument version
of array_relative() would be required, with the signature

I am not sure. Usually need both

when I play with a stack I need

a) FIFO - first element from array and all others without first element
b) LIFO - last element from array and all others without last element

The game with queues is only one use case that I know where I need
access to relative indexed items in array.

Maybe is other, but I don't know it. ??? I don't know why I need a
access to relative indexed items?

Pavel

Post by Florian Pflug
 array_relative(some_array anyarray, first int[], last int[])
Your requirements (a) to (d) are then easily satisfied
a) array_relative(ary, array[0], array[n-1])
b) array_relative(ary, array[0], array[-n-1])
c) array_relative(ary, array[-n], array[-1])
d) array_relative(ary, array[n], array[-1])

what is n?? it's not implementable.

Post by Florian Pflug
The individual function approach might be a tad more readable for
one-dimensional arrays, but they don't scale well to the general
case.
Maybe the OP could comment on whether any of these solutions
would fit his needs?
best regards,
Florian Pflug

[Hannu Valtonen]

Post by Florian Pflug

Post by Pavel Stehule
Sorry, but It isn't too intuitive. Minimally for me. Why you don't
thinking about simple functions with only positive arguments. There
are only four combinations. I don't think we must have only one super
function.
a) get first n items
b) get items without last n items
c) get last n items
d) skip first n items

Now you've moved the goalpost - the OP wanted to access individual
elements, not slices! To support slices, a three-argument version
of array_relative() would be required, with the signature
array_relative(some_array anyarray, first int[], last int[])
Your requirements (a) to (d) are then easily satisfied
a) array_relative(ary, array[0], array[n-1])
b) array_relative(ary, array[0], array[-n-1])
c) array_relative(ary, array[-n], array[-1])
d) array_relative(ary, array[n], array[-1])
The individual function approach might be a tad more readable for
one-dimensional arrays, but they don't scale well to the general
case.
Maybe the OP could comment on whether any of these solutions
would fit his needs?

Hi,

(sorry for the late reply, got lost in my Inbox)

For my main use case I just needed the last element of the array, but I
could see myself needing a slice as well. (i.e. give me the last 5 items
in an array)

So in that sense yes, this would fit the bill.

Hannu Valtonen
Lead Software Architect
Technology Office
F-Secure Corporationhttp://www.F-Secure.com