Movatterモバイル変換

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 inTable 9.61 andTable 9.62. Other, more specialized set-returning functions are described elsewhere in this manual. SeeSection 7.2.1.4 for ways to combine multiple set-returning functions.

Whenstep is positive, zero rows are returned ifstart is greater thanstop. Conversely, whenstep is negative, zero rows are returned ifstart is less thanstop. Zero rows are also returned forNULL inputs. It is an error forstep 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 operatorSELECT 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)

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 for NULL arrays (but valid subscripts are returned for NULL array elements). Some examples follow:

-- basic usageSELECT 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 subquerySELECT * FROM arrays;         a          -------------------- {-1,-2} {100,200,300}(2 rows)SELECT a AS array, s AS subscript, a[s] AS valueFROM (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 arrayCREATE 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 FUNCTIONSELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); unnest2 ---------       1       2       3       4(4 rows)

When a function in theFROM clause is suffixed byWITH ORDINALITY, abigint column is appended to the output 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 asunnest().

-- set returning function WITH ORDINALITYSELECT * 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)