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, hugeint, real, double, decimal(10,2), date or timestamp
and return a set of corresponding values.
| Function | Argument types | Description |
|---|---|---|
| generate_series(first, limit) | tinyint or smallint or int or bigint or hugeint | Generate a series of integer numbers, from first till limit with a increment of 1 |
| generate_series(first, limit, stepsize) | tinyint or smallint or int or bigint or hugeint or real or double or decimal | Generate a series of numbers, from first till limit with increments of stepsize |
| generate_series(first, limit, interval stepsize) | date, interval day or month | Generate a series of dates with increments of interval stepsize |
| generate_series(first, limit, interval stepsize) | timestamp, interval second or day | Generate a series of timestamps with increments of interval stepsize |
To view all available generate_series() function signatures, run query:
select func from sys.functions where name = 'generate_series' and system;
The generated values excludes the limit value.
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 * FROM generate_series(cast('2008-03-01' as date),
cast('2008-04-01' as date), interval '7' day);
+------------+
| value |
+============+
| 2008-03-01 |
| 2008-03-08 |
| 2008-03-15 |
| 2008-03-22 |
| 2008-03-29 |
+------------+
5 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 |
| 2008-03-04 08:00:00.000000 |
+----------------------------+
9 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