If you've ever needed a particular sequence of numbers in PostgreSQL, for example for a pivot table, the only way to generate these has been either to create a temporary sequence or to work with a temporary table generated by your application. Beginning with 8.0, PostgreSQL provides the built-in function generate_series(), a set-returning function which can generate any sequence of numbers.

Use it like this:

test=> SELECT * FROM generate_series(1,3);
 generate_series
-----------------
               1
               2
               3
(3 rows)

test=> SELECT * FROM generate_series(1,6,2);
 generate_series
-----------------
               1
               3
               5
(2 rows)

test=> SELECT * FROM generate_series(3,1,-1);
 generate_series
-----------------
               3
               2
               1
(3 rows)

A practical use could be a statement such as the following, which generates a report of orders for the first seven days of the current month:

   SELECT DATE_TRUNC('month', NOW())::DATE + s.d AS date,
          COUNT(o.*) AS total
     FROM generate_series(0,6) AS s(d)
LEFT JOIN orders o
       ON (o.order_date = (DATE_TRUNC('month', NOW())::DATE + s.d)::TIMESTAMP)
 GROUP BY s.d
 ORDER BY 1

generate_series() is the first built-in function to take advantage of the set-returning functions introduced in version 7.3. Originally created for use in the PostgreSQL backend, it has been made available for general use.