Generator Functions

This section describes table producting built-in functions that possibly return more than one row. Currently the only functions in this class are series generating functions that take as argument a value of type {tinyint, smallint, int, bigint, real, double, decimal(10,2), timestamp} and returns a set of corresponding values.

The sequence excludes the stop value.

FunctionDescription
generate_series(start, stop)Generate a series of numbers, from start till stop with a increment of one
generate_series(start, stop, step)Generate a series of numbers, from start till stop with increments of step size
generate_series(start, stop, step interval)timestamps and intervalGenerate a series of timestamps with increments of interval size

Examples

sql> SELECT * FROM generate_series(2,4);
+-------+
| value |
+=======+
|     2 |
|     3 |
+-------+
2 tuples

sql> SELECT * FROM generate_series(5,1,-2);
+-------+
| value |
+=======+
|     5 |
|     3 |
+-------+
2 tuples

sql> SELECT current_time() + s.a * interval '1' minute FROM generate_series(0,14,7) AS s(a);
+-----------------------+
| %4                    |
+=======================+
| 21:17:20.792948+02:00 |
| 21:24:20.792948+02:00 |
+-----------------------+
2 tuples

sql>SELECT * FROM generate_series(cast('2008-03-01 00:00' as timestamp), 
     cast('2008-03-04 12:00' as timestamp), 
     interval '10' hour);
+----------------------------+
| value                      |
+============================+
| 2008-03-01 00:00:00.000000 |
| 2008-03-01 10:00:00.000000 |
| 2008-03-01 20:00:00.000000 |
| 2008-03-02 06:00:00.000000 |
| 2008-03-02 16:00:00.000000 |
| 2008-03-03 02:00:00.000000 |
| 2008-03-03 12:00:00.000000 |
| 2008-03-03 22:00:00.000000 |
+----------------------------+
8 tuples