begriffs open source - ai-pg/blob - full-docs/txt/functions-srf.txt

   1
   2 9.26. Set Returning Functions #
   3
   4    This section describes functions that possibly return more than one
   5    row. The most widely used functions in this class are series generating
   6    functions, as detailed in Table 9.69 and Table 9.70. Other, more
   7    specialized set-returning functions are described elsewhere in this
   8    manual. See Section 7.2.1.4 for ways to combine multiple set-returning
   9    functions.
  10
  11    Table 9.69. Series Generating Functions
  12
  13    Function
  14
  15    Description
  16
  17    generate_series ( start integer, stop integer [, step integer ] ) →
  18    setof integer
  19
  20    generate_series ( start bigint, stop bigint [, step bigint ] ) → setof
  21    bigint
  22
  23    generate_series ( start numeric, stop numeric [, step numeric ] ) →
  24    setof numeric
  25
  26    Generates a series of values from start to stop, with a step size of
  27    step. step defaults to 1.
  28
  29    generate_series ( start timestamp, stop timestamp, step interval ) →
  30    setof timestamp
  31
  32    generate_series ( start timestamp with time zone, stop timestamp with
  33    time zone, step interval [, timezone text ] ) → setof timestamp with
  34    time zone
  35
  36    Generates a series of values from start to stop, with a step size of
  37    step. In the timezone-aware form, times of day and daylight-savings
  38    adjustments are computed according to the time zone named by the
  39    timezone argument, or the current TimeZone setting if that is omitted.
  40
  41    When step is positive, zero rows are returned if start is greater than
  42    stop. Conversely, when step is negative, zero rows are returned if
  43    start is less than stop. Zero rows are also returned if any input is
  44    NULL. It is an error for step to be zero. Some examples follow:
  45 SELECT * FROM generate_series(2,4);
  46  generate_series
  47 -----------------
  48                2
  49                3
  50                4
  51 (3 rows)
  52
  53 SELECT * FROM generate_series(5,1,-2);
  54  generate_series
  55 -----------------
  56                5
  57                3
  58                1
  59 (3 rows)
  60
  61 SELECT * FROM generate_series(4,3);
  62  generate_series
  63 -----------------
  64 (0 rows)
  65
  66 SELECT generate_series(1.1, 4, 1.3);
  67  generate_series
  68 -----------------
  69              1.1
  70              2.4
  71              3.7
  72 (3 rows)
  73
  74 -- this example relies on the date-plus-integer operator:
  75 SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
  76    dates
  77 ------------
  78  2004-02-05
  79  2004-02-12
  80  2004-02-19
  81 (3 rows)
  82
  83 SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
  84                               '2008-03-04 12:00', '10 hours');
  85    generate_series
  86 ---------------------
  87  2008-03-01 00:00:00
  88  2008-03-01 10:00:00
  89  2008-03-01 20:00:00
  90  2008-03-02 06:00:00
  91  2008-03-02 16:00:00
  92  2008-03-03 02:00:00
  93  2008-03-03 12:00:00
  94  2008-03-03 22:00:00
  95  2008-03-04 08:00:00
  96 (9 rows)
  97
  98 -- this example assumes that TimeZone is set to UTC; note the DST transition:
  99 SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
 100                               '2001-11-01 00:00 -05:00'::timestamptz,
 101                               '1 day'::interval, 'America/New_York');
 102     generate_series
 103 ------------------------
 104  2001-10-22 04:00:00+00
 105  2001-10-23 04:00:00+00
 106  2001-10-24 04:00:00+00
 107  2001-10-25 04:00:00+00
 108  2001-10-26 04:00:00+00
 109  2001-10-27 04:00:00+00
 110  2001-10-28 04:00:00+00
 111  2001-10-29 05:00:00+00
 112  2001-10-30 05:00:00+00
 113  2001-10-31 05:00:00+00
 114  2001-11-01 05:00:00+00
 115 (11 rows)
 116
 117    Table 9.70. Subscript Generating Functions
 118
 119    Function
 120
 121    Description
 122
 123    generate_subscripts ( array anyarray, dim integer ) → setof integer
 124
 125    Generates a series comprising the valid subscripts of the dim'th
 126    dimension of the given array.
 127
 128    generate_subscripts ( array anyarray, dim integer, reverse boolean ) →
 129    setof integer
 130
 131    Generates a series comprising the valid subscripts of the dim'th
 132    dimension of the given array. When reverse is true, returns the series
 133    in reverse order.
 134
 135    generate_subscripts is a convenience function that generates the set of
 136    valid subscripts for the specified dimension of the given array. Zero
 137    rows are returned for arrays that do not have the requested dimension,
 138    or if any input is NULL. Some examples follow:
 139 -- basic usage:
 140 SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
 141  s
 142 ---
 143  1
 144  2
 145  3
 146  4
 147 (4 rows)
 148
 149 -- presenting an array, the subscript and the subscripted
 150 -- value requires a subquery:
 151 SELECT * FROM arrays;
 152          a
 153 --------------------
 154  {-1,-2}
 155  {100,200,300}
 156 (2 rows)
 157
 158 SELECT a AS array, s AS subscript, a[s] AS value
 159 FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
 160      array     | subscript | value
 161 ---------------+-----------+-------
 162  {-1,-2}       |         1 |    -1
 163  {-1,-2}       |         2 |    -2
 164  {100,200,300} |         1 |   100
 165  {100,200,300} |         2 |   200
 166  {100,200,300} |         3 |   300
 167 (5 rows)
 168
 169 -- unnest a 2D array:
 170 CREATE OR REPLACE FUNCTION unnest2(anyarray)
 171 RETURNS SETOF anyelement AS $$
 172 select $1[i][j]
 173    from generate_subscripts($1,1) g1(i),
 174         generate_subscripts($1,2) g2(j);
 175 $$ LANGUAGE sql IMMUTABLE;
 176 CREATE FUNCTION
 177 SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
 178  unnest2
 179 ---------
 180        1
 181        2
 182        3
 183        4
 184 (4 rows)
 185
 186    When a function in the FROM clause is suffixed by WITH ORDINALITY, a
 187    bigint column is appended to the function's output column(s), which
 188    starts from 1 and increments by 1 for each row of the function's
 189    output. This is most useful in the case of set returning functions such
 190    as unnest().
 191 -- set returning function WITH ORDINALITY:
 192 SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
 193        ls        | n
 194 -----------------+----
 195  pg_serial       |  1
 196  pg_twophase     |  2
 197  postmaster.opts |  3
 198  pg_notify       |  4
 199  postgresql.conf |  5
 200  pg_tblspc       |  6
 201  logfile         |  7
 202  base            |  8
 203  postmaster.pid  |  9
 204  pg_ident.conf   | 10
 205  global          | 11
 206  pg_xact         | 12
 207  pg_snapshots    | 13
 208  pg_multixact    | 14
 209  PG_VERSION      | 15
 210  pg_wal          | 16
 211  pg_hba.conf     | 17
 212  pg_stat_tmp     | 18
 213  pg_subtrans     | 19
 214 (19 rows)