This section describes functions that possibly return more than one row. The most widely used functions in this class are series generating functions, as detailed in Table 9.67 and Table 9.68. Other, more specialized set-returning functions are described elsewhere in this manual. See Section 7.2.1.4 for ways to combine multiple set-returning functions.

When step is positive, zero rows are returned if start is greater than stop. Conversely, when step is negative, zero rows are returned if start is less than stop. Zero rows are also returned if any input is NULL. It is an error for step to be zero. Some examples follow:

SELECT * FROM generate_series(2,4);
 generate_series
-----------------
 2
 3
 4
(3 rows)
SELECT * FROM generate_series(5,1,-2);
 generate_series
-----------------
 5
 3
 1
(3 rows)
SELECT * FROM generate_series(4,3);
 generate_series
-----------------
(0 rows)
SELECT generate_series(1.1, 4, 1.3);
 generate_series
-----------------
 1.1
 2.4
 3.7
(3 rows)
-- this example relies on 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)
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)
-- this example assumes that TimeZone is set to UTC; note the DST transition:
SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
 '2001-11-01 00:00 -05:00'::timestamptz,
 '1 day'::interval, 'America/New_York');
 generate_series
------------------------
 2001年10月22日 04:00:00+00
 2001年10月23日 04:00:00+00
 2001年10月24日 04:00:00+00
 2001年10月25日 04:00:00+00
 2001年10月26日 04:00:00+00
 2001年10月27日 04:00:00+00
 2001年10月28日 04:00:00+00
 2001年10月29日 05:00:00+00
 2001年10月30日 05:00:00+00
 2001年10月31日 05:00:00+00
 2001年11月01日 05:00:00+00
(11 rows)

generate_subscripts is a convenience function that generates the set of valid subscripts for the specified dimension of the given array. Zero rows are returned for arrays that do not have the requested dimension, or if any input is NULL. Some examples follow:

-- basic usage:
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
 s
---
 1
 2
 3
 4
(4 rows)
-- presenting an array, the subscript and the subscripted
-- 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:
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)

When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the function's output column(s), which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

-- set returning function WITH ORDINALITY:
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)