Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 8.2

PostgreSQL 8.2.9 Documentation
Prev	Fast Backward	Chapter 9. Functions and Operators	Fast Forward	Next

9.18. Set Returning Functions

This section describes functions that possibly return more than one row. Currently the only functions in this class are series generating functions, as detailed in Table 9-39.

Table 9-39. Series Generating Functions

Function	Argument Type	Return Type	Description
`generate_series(start, stop)`	`int` or `bigint`	`setof int` or `setof bigint` (same as argument type)	Generate a series of values, from `start` to `stop` with a step size of one
`generate_series(start, stop, step)`	`int` or `bigint`	`setof int` or `setof bigint` (same as argument type)	Generate a series of values, from `start` to `stop` with a step size of `step`

When step is positive, zero rows are returned if start is greater than stop. Conversely, when step is negative, zero rows are returned if start is less than stop. Zero rows are also returned for NULL inputs. It is an error for step to be zero. Some examples follow:

select * from generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

select * from generate_series(5,1,-2);
 generate_series
-----------------
               5
               3
               1
(3 rows)

select * from generate_series(4,3);
 generate_series
-----------------
(0 rows)

select current_date + s.a as dates from generate_series(0,14,7) as s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

Prev	Home	Next
Row and Array Comparisons	Up	System Information Functions

User Comments

Andrew Hammond

11 Apr 2007 17:18:39

If you have a bunch of incidents in a table that you want to turn into a table of frequencies, and you don't want non-existing group-by clauses to disappear, outer join them against a generated series.

SELECT
  TIMESTAMP WITH TIME ZONE 'epoch' + s.a * INTERVAL '1 second'
, COALESCE(b.freq, 0) AS freq
FROM generate_series(EXTRACT(epoch FROM today - '1 month')
,     EXTRACT(epoch FROM today), 24 * 60 * 60) AS s(a)
LEFT OUTER JOIN (
    SELECT extract(epoch from date_trunc('day', when) AS when
      , count(*) AS freq
    FROM incident_table
    GROUP BY 1
) AS b
ON (s.a = b.when);

This is would be much less grotesque if there was a generate_series(start_timestamp, stop_timestamp, step_interval) function.

Erwin Brandstetter

16 May 2007 0:33:57

@Andrew Hammond
There is a less grotesque way to do what you are asking for. Might be of interest to the general public. Try the following syntax:
   SELECT now() + generate_series(1,10) * interval '3 seconds';

Or, equivalently:
   SELECT now() + generate_series(1,30,3) * interval '1 seconds';

Or, for a simpler case of a series of dates:
  SELECT current_date + generate_series(1,10);

HTH, Erwin

Erwin Brandstetter

16 May 2007 0:49:41

Alias for generate_series(...):

This is a bit confusing. If I write:
SELECT * FROM generate_series(1, 10) AS x;
then then "x" becomes the alias for the column AND the relation.
You can verify this by running:
SELECT * from generate_series(1, 10) AS x ORDER BY x.x DESC;

New comments cannot be added to old documentation versions.