Thread: Return last value in of array (PostgreSQL 7.2.1)
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.
> 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
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.
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.
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
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.