| PostgreSQL 8.2.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | Next |
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)
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.
@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
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;