Return last value in of array (PostgreSQL 7.2.1)

Поиск
Список
Период
Сортировка
Искать

Return last value in of array (PostgreSQL 7.2.1)

От:
Erwin Brandstetter <Brsaz@gmzx.at>
Дата:
Hi Newsgroup!

I can't believe I have found a good solution here. I want to return the 
last value of an array with an SQL statement, while I do not know, how many 
items the array holds (u can never be sure of that since u can redim an 
array in PostgreSQL whenever u like..).

That is what i came up with:


select my_array_field[substr(array_dims(my_array_field), 4, 1)]
from my_table;


But this only works, as long as the upper boundary is from 1 to 9 (one 
digit). Besides it looks ugly and will perform likewise.

Did I miss something here?
Is there any function returning more sensible data than array_dims 
(returning something like '[1:4]')?

Grateful for any hints.
Regards
Erwin Brandstetter

-- 
no z in my mail.

Re: Return last value in of array (PostgreSQL 7.2.1)

От:
Steve Crawford <scrawford@pinpointresearch.com>
Дата:
> I can't believe I have found a good solution here. I want to return
> the last value of an array with an SQL statement...
> That is what i came up with:
>
> select my_array_field[substr(array_dims(my_array_field), 4, 1)]
> from my_table;
>
> But this only works, as long as the upper boundary is from 1 to 9
> (one digit). Besides it looks ugly and will perform likewise.
>
> Did I miss something here?
> Is there any function returning more sensible data than array_dims
> (returning something like '[1:4]')?

IIRC I heard talk of new max and min array dimension functions in 
upcoming releases. I'm tired so there may be better or more elegant 
solutions but this one works (r is the array):

r[rtrim(substring(array_dims(r), position(':' in 
array_dims(r))+1),']')::int]

Cheers,
Steve

Re: Return last value in of array (PostgreSQL 7.2.1)

От:
Erwin Brandstetter <Brsaz@gmzx.at>
Дата:
scrawford@pinpointresearch.com (Steve Crawford) wrote in
news:200309231746.13757.scrawford@pinpointresearch.com: 

> IIRC I heard talk of new max and min array dimension functions in 
> upcoming releases. I'm tired so there may be better or more elegant 
> solutions but this one works (r is the array):
> 
> r[rtrim(substring(array_dims(r), position(':' in 
> array_dims(r))+1),']')::int]

This works and is an improvement to my previous version as it allows for 
upper boundaries with any number of digits. Thank you!

Still the solution looks ugly and will perform likewise. PostgreSQL 
urgently needs improvment on array handling. Hopefully new versions take 
care of that (haven't tested 7.3+).

BTW: Have been away for some days, so my reply is late, sorry.


Regards
Erwin Brandstetter

-- 
no z in my mail.

Re: Return last value in of array (PostgreSQL 7.2.1)

От:
Erwin Brandstetter <Brsaz@gmzx.at>
Дата:
Not that it makes much of a difference, but i guess the following code 
might work slightly better:

r[(substring(array_dims(r), strpos(array_dims(r), ':') + 1, 
(length(array_dims(r)) - strpos(array_dims(r), ':')) - 1))::int]


Regards
Erwin Brandstetter
-- 
no z in my mail.

Re: Return last value in of array (PostgreSQL 7.2.1)

От:
Joe Conway <mail@joeconway.com>
Дата:
Erwin Brandstetter wrote:
> Still the solution looks ugly and will perform likewise. PostgreSQL 
> urgently needs improvment on array handling. Hopefully new versions take 
> care of that (haven't tested 7.3+).

7.4 array functionality is significantly improved. Still room for more 
improvements, but well worth a look if you need to make use of arrays. 
See the following (not sure these urls will survive wrapping):

http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
http://developer.postgresql.org/docs/postgres/arrays.html
http://developer.postgresql.org/docs/postgres/functions-array.html
http://developer.postgresql.org/docs/postgres/functions-comparisons.html

HTH,

Joe

Re: Return last value in of array (PostgreSQL 7.2.1)

От:
Erwin Brandstetter <Brsaz@gmzx.at>
Дата:
mail@joeconway.com (Joe Conway) wrote in 
news:3F80D8B1.7070407@joeconway.com:

> http://developer.postgresql.org/docs/postgres/functions-array.html

Very good!

PostgreSQL 7.4 will provide the functions "array_lower" and "array_upper" 
doing exactly what I was looking for.

Unfortunately I have to implement the functionality now. Can't wait till 
7.4 is availiable on our productive system. :(

Regards
Erwin

-- 
no z in my mail.
FAQ