9.24. 集合回傳函式
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.58andTable 9.59. Other, more specialized set-returning functions are described elsewhere in this manual. SeeSection 7.2.1.4for ways to combine multiple set-returning functions.
Table 9.58. Series Generating Functions
Function
Argument Type
Return Type
Description
generate_series(start
,stop
)
int
,bigint
ornumeric
setof int
,setof bigint
, orsetof numeric
(same as argument type)
Generate a series of values, fromstart
_tostop
_with a step size of one
generate_series(start
,stop
,step
)
int
,bigint
ornumeric
setof int
,setof bigint
orsetof numeric
(same as argument type)
Generate a series of values, fromstart
_tostop
with a step size ofstep
_
generate_series(start
,stop
,stepinterval
)
timestamp
ortimestamp with time zone
setof timestamp
orsetof timestamp with time zone
(same as argument type)
Generate a series of values, fromstart
_tostop
with a step size ofstep
_
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 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)
Table 9.59. Subscript Generating Functions
Function
Return Type
Description
generate_subscripts(array anyarray
,dim int
)
setof int
Generate a series comprising the given array's subscripts.
generate_subscripts(array anyarray
,dim int
,reverse boolean
)
setof int
Generate a series comprising the given array's subscripts. When_reverse
_is true, the series is returned in reverse order.
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 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 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 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)
Last updated
Was this helpful?