Set-returning functions return more than one row per call. The most widely used are series-generating functions. Other specialized set-returning functions are described elsewhere in this manual.
Series-generating functions
generate_series produces a sequence of values by stepping from a start value to a stop value.
Table 1. Series-generating functions
| Signature | Returns |
|---|---|
generate_series(start integer, stop integer [, step integer]) | SETOF integer |
generate_series(start bigint, stop bigint [, step bigint]) | SETOF bigint |
generate_series(start numeric, stop numeric [, step numeric]) | SETOF numeric |
generate_series(start timestamp, stop timestamp, step interval) | SETOF timestamp |
generate_series(start timestamp with time zone, stop timestamp with time zone, step interval) | SETOF timestamp with time zone |
Parameters
| Parameter | Description | Default |
|---|---|---|
start | First value in the series | — |
stop | Last value in the series (inclusive) | — |
step | Increment between values | 1 |
Boundary behavior
When
stepis positive, zero rows are returned ifstart>stop.When
stepis negative, zero rows are returned ifstart<stop.Zero rows are returned if any input is
NULL.A
stepof zero is an error.
Examples
-- Integer series, ascending
SELECT * FROM generate_series(2, 4);
generate_series
-----------------
2
3
4
(3 rows)
-- Integer series, descending with negative step
SELECT * FROM generate_series(5, 1, -2);
generate_series
-----------------
5
3
1
(3 rows)
-- Empty result when start > stop and step is positive
SELECT * FROM generate_series(4, 3);
generate_series
-----------------
(0 rows)
-- Numeric series
SELECT generate_series(1.1, 4, 1.3);
generate_series
-----------------
1.1
2.4
3.7
(3 rows)
-- Date series using the date-plus-integer operator
SELECT current_date + s.a AS dates FROM generate_series(0, 14, 7) AS s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
-- Timestamp series in 10-hour steps
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)Subscript-generating functions
generate_subscripts returns the valid subscripts for a given dimension of an array.
Table 2. Subscript-generating functions
| Signature | Returns |
|---|---|
generate_subscripts(array anyarray, dim integer) | SETOF integer |
generate_subscripts(array anyarray, dim integer, reverse boolean) | SETOF integer |
Parameters
| Parameter | Description |
|---|---|
array | The array to generate subscripts for |
dim | The array dimension to iterate over |
reverse | When true, returns subscripts in reverse order |
Zero rows are returned if the array does not have the requested dimension, or if any input is NULL.
Examples
-- Basic usage: subscripts of a one-dimensional integer array
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
(4 rows)
-- Presenting an array alongside each subscript and its value requires a subquery
SELECT * FROM arrays;
a
--------------------
{-1,-2}
{100,200,300}
(2 rows)
SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
array | subscript | value
---------------+-----------+-------
{-1,-2} | 1 | -1
{-1,-2} | 2 | -2
{100,200,300} | 1 | 100
{100,200,300} | 2 | 200
{100,200,300} | 3 | 300
(5 rows)
-- Unnest a 2D array using generate_subscripts on each dimension
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
unnest2
---------
1
2
3
4
(4 rows)WITH ORDINALITY
Append WITH ORDINALITY to any set-returning function in the FROM clause to add a row-number column. The added column has type bigint, starts at 1, and increments by 1 for each output row. This is most useful with functions like unnest().
Example
-- List directory entries with ordinal row numbers
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls, n);
ls | n
-----------------+----
pg_serial | 1
pg_twophase | 2
postmaster.opts | 3
pg_notify | 4
postgresql.conf | 5
pg_tblspc | 6
logfile | 7
base | 8
postmaster.pid | 9
pg_ident.conf | 10
global | 11
pg_xact | 12
pg_snapshots | 13
pg_multixact | 14
PG_VERSION | 15
pg_wal | 16
pg_hba.conf | 17
pg_stat_tmp | 18
pg_subtrans | 19
(19 rows)