9.25. 集合回傳函式

本節介紹可以回傳多個資料列的函數。此類中使用最廣泛的函數是序列生成函數,如 Table 9.61Table 9.62 所述。其他更專門的集合回傳函數在本手冊的其他地方介紹。有關組合多個集合回傳函數的方法,請參見第 7.2.1.4 節

Table 9.61. Series Generating Functions

Function

Argument Type

Return Type

Description

generate_series(start, stop)

int, bigint or numeric

setof int, setof bigint, or setof numeric (same as argument type)

從 start 到 stop 產生成一系列的值,間隔為 1

generate_series(start, stop, step)

int, bigint or numeric

setof int, setof bigint or setof numeric (same as argument type)

產生一系列的值,從 start 到 end,間隔為 step

generate_series(start, stop, step interval)

timestamp or timestamp with time zone

setof timestamp or setof timestamp with time zone (same as argument type)

產生一系列的值,從 start 到 end,間隔為 step

當 step 為正時,如果 start 大於 stop 則回傳零筆資料。相反地,當 step 為負時,如果 start 小於 stop 也回傳零筆資料。NULL 的輸入也回傳零筆資料。 step 為零是錯誤的。以下是一些範例:

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.62. Subscript Generating Functions

Function

Return Type

Description

generate_subscripts(array anyarray, dim int)

setof int

產生成一個包含給定陣列索引的系列內容。

generate_subscripts(array anyarray, dim int, reverse boolean)

setof int

產生一個包含給定陣列索引的序列內容。當 reverse 為 true 時,將以相反的順序回傳該序列。

generate_subscripts 是一個很方便的函數,用於為給定陣列的指定維度產成一組有效的索引內容。對於沒有所請求維數的陣列或 NULL 陣列,回傳零筆資料(但是對於 NULL 陣列元素,回傳有效的索引)。以下是一些範例:

-- 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)

當 FROM 子句中的函數加上 WITH ORDINALITY 時,一個 bigint 欄位將附加到輸出資料中,該欄位從 1 開始,並針對函數輸出的每一筆資料以 1 遞增。這對集合回傳函數中的 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)
Edit on GitHub