Set-returning functions

​

generate_series()

The generate_series() function in PostgreSQL is a set-returning function that generates a series of values, based on the start and end values defined by the user. It is useful for generating test data or for creating a sequence of numbers or timestamps.

The syntax for the generate_series() function is as follows:

SELECT *
FROM generate_series(start, stop, step);

start, stop, and step can be of type integer, bigint, numeric, or timestamp.

• start is the first value in the series.
• stop is the last value in the series.
• step is optional unless start and stop are of type timestamp. It is the increment value. If it is omitted, the default step value is 1.

Here is an example of how you can use the generate_series() function to generate a series of numbers:

SELECT *
FROM generate_series(1, 5);

The result looks like this:

1
2
3
4
5

And here is an example with a step increment of 2:

SELECT *
FROM generate_series(2, 10, 2);

The result looks like this:

2
4
6
8
10

Here is an example of how you can use the generate_series() function to generate a series of timestamps:

SELECT generate_series
FROM generate_series(
    '2008-03-01 00:00:00'::TIMESTAMP,
    '2008-03-04 12:00:00'::TIMESTAMP,
    interval '12' hour
);

The result looks like this:

2008-03-01 00:00:00
2008-03-01 12:00:00
2008-03-02 00:00:00
2008-03-02 12:00:00
2008-03-03 00:00:00
2008-03-03 12:00:00
2008-03-04 00:00:00
2008-03-04 12:00:00

Except for generating a static set of values, RisingWave also supports continuously generating timestamps at specified intervals into a materialized view. To achieve this, use now() as the stop parameter in the generate_series() function. For example:

CREATE MATERIALIZED VIEW mv AS
SELECT * FROM generate_series(
  '2020-01-01 00:00:00'::timestamptz,
  now(),
  interval '1 hour'
);

And you should follow the following syntax:

• start must be a constant expression of type timestamptz.
• stop must be now().
• step must be a constant expression of type interval.

The timestamps generated by the above example will look like this:

2020-01-01 00:00:00
2020-01-01 01:00:00
2020-01-01 02:00:00
2020-01-01 03:00:00
...

​

range()

The range() function in PostgreSQL is a set-returning function that generates a series of values, based on the start and end values defined by the user. The end value is not included, unlike generate_series(). It is useful for generating test data or for creating a sequence of numbers or timestamps.

The syntax for the range() function is as follows:

SELECT *
FROM range(start, stop, step);

start, stop, and step can be of type integer, bigint, numeric, or `timestamp.

• start is the first value in the series.
• stop is the last value in the series.
• step is optional unless start and stop are of type timestamp. It is the increment value. If it is omitted, the default step value is 1.

Here is an example of how you can use the range() function to generate a series of numbers:

SELECT *
FROM range(1, 4);

The result looks like this:

1
2
3

And here is an example with a step increment of 0.5:

SELECT *
FROM range(0.1, 2.1, 0.5);

The result looks like this:

0.1
0.6
1.1
1.6

Here is an example of how you can use the range() function to generate a series of timestamps:

SELECT range
FROM range(
    '2008-03-01 00:00:00'::TIMESTAMP,
    '2008-03-04 12:00:00'::TIMESTAMP,
    interval '12' hour
);

The result looks like this:

2008-03-01 00:00:00
2008-03-01 12:00:00
2008-03-02 00:00:00
2008-03-02 12:00:00
2008-03-03 00:00:00
2008-03-03 12:00:00
2008-03-04 00:00:00

​

_pg_expandarray()

The _pg_expandarray function takes an array as input and expands it into a set of rows, providing values and their corresponding indices within the array. Ensure that information_schema is in the search path to access the _pg_expandarray function.

Example:

SELECT * FROM information_schema._pg_expandarray(Array['a','b','c']);

 x | n
---+---
 a | 1
 b | 2
 c | 3
(3 rows)

Columns in the returned set of rows:

• x: The value within the array.
• n: The index of the value within the array.