Product Documentation

Appearance

All platforms

Set Returning Functions ​

Prerequisite: while the feature is in beta, you must explicitly enable it by setting enable_full_funcscan and enable_full_lateral_join to ON.

Note: To use Set Returning Functions (SRFs), both configuration parameters must be enabled together.

Below is the list of Set Returning Functions (SRFs) that can be used:

FunctionDescriptionOutput
generate_series(start,stop,step)Generates a sequence of numbers from start to stop with a step size of stepRow of integers
FLATTEN(jsonb_expression)For details, see FLATTEN.A JSONB object with the fields "index", "key" and "value" for each element contained in the input JSONB.

SRFs can return zero, one or multiple rows. For example:

sql
SELECT * FROM generate_series(3, 100, 29);
sql
generate_series
-----------------
               3
              32
              61
              90
(4 rows)

Supported contexts ​

Set returning functions (SRFs) are not supported in the same contexts as normal functions. Using an SRF in a place where it is not allowed results in an error:

sql
CREATE TABLE t (start INT, stop INT, step INT);

SELECT generate_series(start, stop, step) FROM t;
-- ERROR:  set-valued function called in context that cannot accept a set

SRFs in FROM clause ​

SRFs can be used in the FROM clause, as shown in the first example on this page

sql
SELECT * FROM generate_series(3, 100, 29);
sql
generate_series
-----------------
              3
             32
             61
             90
(4 rows)

SRFs in Joins ​

SRFs can be used in joins. In the following examples, we refer to table t:

sql
CREATE TABLE t (start INT, stop INT, step INT);

INSERT INTO t VALUES (3, 100, 29), (7, 8, 9), (3, 2, 1);

The arguments from table t can be forwarded to generate_series by performing a join:

sql
SELECT * FROM t JOIN generate_series(start, stop, step) ON TRUE;
sql
start | stop | step | generate_series
-------+------+------+-----------------
     3 |  100 |   29 |               3
     3 |  100 |   29 |              32
     3 |  100 |   29 |              61
     3 |  100 |   29 |              90
     7 |    8 |    9 |               7
(5 rows)

When using an SRF in a JOIN, a condition must be provided, which is why there is an ON TRUE part at the end of the query.

There is another way to write queries with no join condition that is more concise but less powerful. The previous query can be rewritten as shown below, giving the same results:

sql
SELECT * FROM t, generate_series(start, stop, step);
sql
start | stop | step | generate_series
-------+------+------+-----------------
     3 |  100 |   29 |               3
     3 |  100 |   29 |              32
     3 |  100 |   29 |              61
     3 |  100 |   29 |              90
     7 |    8 |    9 |               7
(5 rows)

In the previous examples, the generate_series call for the row with (start = 3, stop = 2, step = 1) did not generate any values and thus the join did not generate any values for that row.

Different behavior can be achieved by using a different kind of join, such as a left join:

sql
SELECT * FROM t LEFT JOIN generate_series(start, stop, step) ON TRUE;
sql
start | stop | step | generate_series
-------+------+------+-----------------
     3 |  100 |   29 |               3
     3 |  100 |   29 |              32
     3 |  100 |   29 |              61
     3 |  100 |   29 |              90
     7 |    8 |    9 |               7
     3 |    2 |    1 |
(6 rows)