In this issue we shall be celebrating the arrival of PostgreSQL 8.4 by showcasing the new treats that PostgreSQL 8.4 has to offer. Although 8.4 has some nice big ticket items like Windowing Functions which we briefly covered numerous times and Common Table Expressions, it also has some small ticket items. These small ticket items while small, are perhaps more useful than even the big ticket ones because they are more commonly used constructs.

In this article we shall introduce the new unnest() function which makes converting an array to a table like structure not only easier, but much more efficient. We will also be covering the new enhancements to our favorite function the generate_series().

In the olden days (like before 8.4) when you wanted to convert an array to a table, you would do something like this.

SELECT myarray[i] as element
 FROM  (SELECT ARRAY['johnny','be','good','or','you be bad'] As myarray) as foo
  CROSS JOIN generate_series(1, 5) As i;

	 

With the new unnest function we can do:

SELECT unnest(ARRAY['johnny','be','good','or','you be bad']) as element;
--or
SELECT element
 FROM unnest(ARRAY['johnny','be','good','or','you be bad']) As element;
            
--All of which return

 element
-----------
johnny
be
good
or
you be bad
	

Now while the unnest function has made transforming arrays into tables much easier, you lost the iterator with unnest, so generate_series is still useful under certain conditions. Wait a minute though generate_series has gotten a little bit nicer too. Now you have dates and timestamps.

In the olden days when you wanted a date series that iterates every 10 days up to a certain date, you'd first have to figure out how many sequences you needed or if you were lazy you would overshoot and limit like below:

SELECT CAST('2009-01-01' As date) + CAST((i || ' days') As interval) As aday
 FROM generate_series(0,1000,10) As i
 WHERE CAST('2009-01-01' As date) + CAST((i || ' days') As interval) <= CAST('2009-12-01' As date);

Now you can do this:

	
SELECT CAST(generate_series(CAST('2009-01-01' As date), 
    CAST('2009-12-01' As date), '10 days') as date) As aday;
        
--Both of which return


    aday
------------
 2009-01-01
 2009-01-11
 2009-01-21
 2009-01-31
 2009-02-10
 2009-02-20
 2009-03-02
:
:
 2009-11-07
 2009-11-17
 2009-11-27