PostgreSQL 8.4 unnest and 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