This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 8.0 / 8.1 / 8.2 / 8.3 / 8.4

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)

Comments


April 11, 2007, 5:18 p.m.

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.


May 16, 2007, 12:33 a.m.

@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


May 16, 2007, 12:49 a.m.

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;

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group