January 10th, 2016 by depesz | Tags: , , , , | No comments »
Did it help? If yes - maybe you can help me?

On 18th of December, Teodor Sigaev committed patch:

Allow to omit boundaries in array subscript
 
Allow to omiy lower or upper or both boundaries in array subscript
for selecting slice of array.
 
Author: YUriy Zhuravlev

I believe that PostgreSQL arrays could use some serious revamping to make them more useful. I don't expect any kind of full rewrite, but every single steps makes me happy. With this in mind – this patch is a nice thing.

Basically, if you ever wanted to get slice of array from PostgreSQL, you used syntax like:

$ select ('{a,b,c,d,e,f,g}'::text[])[2:4];
 text   
---------
 {b,c,d}
(1 row)

Simple, and to the point.

But there is a place for improvement. For example – getting first, or last elements, could have been made simpler.

In case of first elements you had to:

$ select ('{a,b,c,d,e,f,g}'::text[])[1:3];
  text
---------
 {a,b,c}
(1 row)

In case of last elements, it was more complicated, because you had to know length, even if you wanted “all, from 4th element on":

$ select ('{a,b,c,d,e,f,g}'::text[])[4:7];
   text    
-----------
 {d,e,f,g}
(1 row)

Now, with this new patch, we can get rid of the 1: or :(length-of-array) parts:

$ select ('{a,b,c,d,e,f,g}'::text[])[:3];
  text   
---------
 {a,b,c}
(1 row)
 
$ select ('{a,b,c,d,e,f,g}'::text[])[4:];
   text    
-----------
 {d,e,f,g}
(1 row)

Maybe not much, but any improvement there is a nice thing.

If I might suggest, it would be great to have ability to get “last-n-elements", perhaps using, like in Perl, negative notation, like:

select ('{a,b,c,d,e,f,g}'::text[])[-3:];

Would return last 3 elements?

Leave a comment