Hologres:Set returning functions

The following table describes the set returning functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see Set Returning Functions in the PostgreSQL documentation.

GENERATE_SERIES

• GENERATE_SERIES(start, stop [,step])

  • Description: Generates a numeric sequence with a specified step size from the value specified by the start parameter to the value specified by the stop parameter.

    GENERATE_SERIES(start, stop[, step])

  • Parameters:

    start: required. The start value. The value of this parameter is of the INT, BIGINT, or NUMERIC data type.

    stop: required. The end value. The value of this parameter is of the INT, BIGINT, or NUMERIC data type.

    step: optional. The step size. The value of this parameter is of the INT, BIGINT, or NUMERIC data type. If you do not configure this parameter, the default value 1 is used.

  • Return value:

    A set of numeric values of the same data type as input parameters is returned.

  • Examples:

    • Example 1:

      SELECT GENERATE_SERIES(2, 4);

      The following result is returned:

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

    • Example 2:

      SELECT GENERATE_SERIES(5, 1, -2);

      The following result is returned:

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

• GENERATE_SERIES(start, stop, step INTERVAL)

  • Description: Generates a date sequence with a specified step size from the value specified by the start parameter to the value specified by the stop parameter.

    GENERATE_SERIES(start, stop, step INTERVAL)

  • Parameters:

    start: required. The start value. The value of this parameter is of the TIMESTAMP or TIMESTAMP WITH TIME ZONE data type.

    stop: required. The end value. The value of this parameter is of the TIMESTAMP or TIMESTAMP WITH TIME ZONE data type.

    step: required. The step size (time interval). The value of this parameter is of the INTERVAL data type.

  • Return value:

    A set of date values of the same data type as input parameters is returned.

  • Example:

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

    The following result is returned:

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

GENERATE_SUBSCRIPTS

• Description: Generates a set of valid subscripts for the specified dimension of a given array.

  GENERATE_SUBSCRIPTS(anyarray, dim)

• Parameters:

  anyarray: required. A multidimensional array of any type.

  dim: required. The dimension of the multidimensional array. The value of this parameter is of the INT type.

• Return value:

  A set of integers is returned.

• Example:

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

  The following result is returned:

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