April 29th, 2008 by depesz | Tags: , , , , | No comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

Yesterday, Alvaro Herrera committed patch written by Pavel Stehule which adds generate_subscripts(…) function.

Commit message:

Add generate_subscripts, a series-generation function which generates an
array's subscripts.
Pavel Stehule, some editorialization by me.

This function is basically nicer way to iterate over array elements.

For example, to convert array of integers to record set with these integers You would have to do something like this:

SELECT i[s] FROM (
SELECT i, generate_series(array_lower(x.i,1), array_upper(x.i,1)) as s
FROM (VALUES ('{2,4,8,16,32}'::INT4[])) as x(i)
) as q;

Now, it can be written in a bit simpler way:

SELECT i[s] FROM (
SELECT i, generate_subscripts(x.i,1) as s
FROM (VALUES ('{2,4,8,16,32}'::INT4[])) as x(i)
) as q;

As You can see, generate_subscripts is just a syntax sugar for generate_series(array_lower(), array_upper()). But it's nice syntax sugar, and I guess it will make the code more readable.

Leave a comment