9.24. 集合回傳函式

本節介紹可以回傳多個資料列的函數。此類中使用最廣泛的函數是序列生成函數，如 Table 9.61和 Table 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 為零是錯誤的。以下是一些範例：

  1. SELECT * FROM generate_series(2,4);
  2.  generate_series
  3. -----------------
  4.                2
  5.                3
  6.                4
  7. (3 rows)
  9. SELECT * FROM generate_series(5,1,-2);
  10.  generate_series
  11. -----------------
  12.                5
  13.                3
  14.                1
  15. (3 rows)
  17. SELECT * FROM generate_series(4,3);
  18.  generate_series
  19. -----------------
  20. (0 rows)
  22. SELECT generate_series(1.1, 4, 1.3);
  23.  generate_series 
  24. -----------------
  25.              1.1
  26.              2.4
  27.              3.7
  28. (3 rows)
  30. -- this example relies on the date-plus-integer operator
  31. SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
  32.    dates
  33. ------------
  34.  2004-02-05
  35.  2004-02-12
  36.  2004-02-19
  37. (3 rows)
  39. SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
  40.                               '2008-03-04 12:00', '10 hours');
  41.    generate_series   
  42. ---------------------
  43.  2008-03-01 00:00:00
  44.  2008-03-01 10:00:00
  45.  2008-03-01 20:00:00
  46.  2008-03-02 06:00:00
  47.  2008-03-02 16:00:00
  48.  2008-03-03 02:00:00
  49.  2008-03-03 12:00:00
  50.  2008-03-03 22:00:00
  51.  2008-03-04 08:00:00
  52. (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 陣列元素，回傳有效的索引）。以下是一些範例：

  1. -- basic usage
  2. SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
  3.  s 
  4. ---
  5.  1
  6.  2
  7.  3
  8.  4
  9. (4 rows)
  11. -- presenting an array, the subscript and the subscripted
  12. -- value requires a subquery
  13. SELECT * FROM arrays;
  14.          a          
  15. --------------------
  16.  {-1,-2}
  17.  {100,200,300}
  18. (2 rows)
  20. SELECT a AS array, s AS subscript, a[s] AS value
  21. FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
  22.      array     | subscript | value
  23. ---------------+-----------+-------
  24.  {-1,-2}       |         1 |    -1
  25.  {-1,-2}       |         2 |    -2
  26.  {100,200,300} |         1 |   100
  27.  {100,200,300} |         2 |   200
  28.  {100,200,300} |         3 |   300
  29. (5 rows)
  31. -- unnest a 2D array
  32. CREATE OR REPLACE FUNCTION unnest2(anyarray)
  33. RETURNS SETOF anyelement AS $$
  34. select $1[i][j]
  35.    from generate_subscripts($1,1) g1(i),
  36.         generate_subscripts($1,2) g2(j);
  37. $$ LANGUAGE sql IMMUTABLE;
  38. CREATE FUNCTION
  39. SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
  40.  unnest2 
  41. ---------
  42.        1
  43.        2
  44.        3
  45.        4
  46. (4 rows)

當 FROM 子句中的函數加上 WITH ORDINALITY 時，一個 bigint 欄位將附加到輸出資料中，該欄位從 1 開始，並針對函數輸出的每一筆資料以 1 遞增。這對集合回傳函數中的 unnest() 特別有用。

  1. -- set returning function WITH ORDINALITY
  2. SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
  3.        ls        | n
  4. -----------------+----
  5.  pg_serial       |  1
  6.  pg_twophase     |  2
  7.  postmaster.opts |  3
  8.  pg_notify       |  4
  9.  postgresql.conf |  5
  10.  pg_tblspc       |  6
  11.  logfile         |  7
  12.  base            |  8
  13.  postmaster.pid  |  9
  14.  pg_ident.conf   | 10
  15.  global          | 11
  16.  pg_xact         | 12
  17.  pg_snapshots    | 13
  18.  pg_multixact    | 14
  19.  PG_VERSION      | 15
  20.  pg_wal          | 16
  21.  pg_hba.conf     | 17
  22.  pg_stat_tmp     | 18
  23.  pg_subtrans     | 19
  24. (19 rows)