Generate Sequences & Subscripts with Set Returning Functions - Hologres

Hologres supports a subset of PostgreSQL set-returning functions. These functions expand a single call into a set of rows, making them useful for generating sequences and array subscripts inline in SQL queries.

For the full PostgreSQL specification, see Set Returning Functions in the PostgreSQL documentation.

Supported functions

Function	Argument types	Return type	Description
GENERATE_SERIES(start, stop [, step])	INT, BIGINT, or NUMERIC	setof INT, BIGINT, or NUMERIC	Generates a numeric sequence from `start` to `stop`.
GENERATE_SERIES(start, stop, step INTERVAL)	TIMESTAMP or TIMESTAMP WITH TIME ZONE	setof TIMESTAMP or TIMESTAMP WITH TIME ZONE	Generates a timestamp sequence from `start` to `stop`.
GENERATE_SUBSCRIPTS(anyarray, dim)	Any array type, INT	setof INT	Generates the valid subscripts for one dimension of an array.

GENERATE_SERIES

Generates a sequence of values from start to stop, stepping by step. Both start and stop are inclusive.

Signatures

GENERATE_SERIES(start, stop [, step])            -- numeric sequence
GENERATE_SERIES(start, stop, step INTERVAL)      -- timestamp sequence

Numeric sequence

Generates a sequence of INT, BIGINT, or NUMERIC values.

Parameters

Parameter	Required	Data type	Description
`start`	Yes	INT, BIGINT, or NUMERIC	The first value in the sequence.
`stop`	Yes	INT, BIGINT, or NUMERIC	The last value in the sequence (inclusive).
`step`	No	INT, BIGINT, or NUMERIC	The increment between values. Defaults to `1`.

Return type: A set of values with the same data type as the input parameters.

Examples

Ascending sequence with the default step:

SELECT GENERATE_SERIES(2, 4);

 generate_series
-----------------
               2
               3
               4

Descending sequence with a negative step:

SELECT GENERATE_SERIES(5, 1, -2);

 generate_series
-----------------
               5
               3
               1

Timestamp sequence

Generates a sequence of TIMESTAMP or TIMESTAMP WITH TIME ZONE values.

Parameters

Parameter	Required	Data type	Description
`start`	Yes	TIMESTAMP or TIMESTAMP WITH TIME ZONE	The first value in the sequence.
`stop`	Yes	TIMESTAMP or TIMESTAMP WITH TIME ZONE	The last value in the sequence (inclusive).
`step`	Yes	INTERVAL	The time interval between values.

Return type: A set of values with the same data type as the input parameters.

Example

SELECT GENERATE_SERIES('2024-10-01 00:00'::timestamp, '2024-10-01 12:00', '10 hours');

   generate_series
---------------------
 2024-10-01 00:00:00
 2024-10-01 10:00:00

GENERATE_SUBSCRIPTS

Generates the valid subscripts for a given dimension of an array. Use this function to iterate over array elements by index.

Signature

GENERATE_SUBSCRIPTS(anyarray, dim)

Parameters

Parameter	Required	Data type	Description
`anyarray`	Yes	Any array type	A multidimensional array of any type whose subscripts are generated.
`dim`	Yes	INT	The dimension of the array to generate subscripts for.

Return type: A set of integers representing the valid subscripts.

Example

SELECT GENERATE_SUBSCRIPTS('{NULL,1,NULL,2}'::int[], 1);

 generate_subscripts
---------------------
                   1
                   2
                   3
                   4