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

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.26. Set Returning Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-comparisons.html" title="9.25. Row and Array Comparisons" /><link rel="next" href="functions-info.html" title="9.27. System Information Functions and Operators" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.26. Set Returning Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-comparisons.html" title="9.25. Row and Array Comparisons">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-info.html" title="9.27. System Information Functions and Operators">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-SRF"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.26. Set Returning Functions <a href="#FUNCTIONS-SRF" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.32.2" class="indexterm"></a><p>
   3    This section describes functions that possibly return more than one row.
   4    The most widely used functions in this class are series generating
   5    functions, as detailed in <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SERIES" title="Table 9.69. Series Generating Functions">Table 9.69</a> and
   6    <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.70. Subscript Generating Functions">Table 9.70</a>.  Other, more specialized
   7    set-returning functions are described elsewhere in this manual.
   8    See <a class="xref" href="queries-table-expressions.html#QUERIES-TABLEFUNCTIONS" title="7.2.1.4. Table Functions">Section 7.2.1.4</a> for ways to combine multiple
   9    set-returning functions.
  10   </p><div class="table" id="FUNCTIONS-SRF-SERIES"><p class="title"><strong>Table 9.69. Series Generating Functions</strong></p><div class="table-contents"><table class="table" summary="Series Generating Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
  11         Function
  12        </p>
  13        <p>
  14         Description
  15        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
  16         <a id="id-1.5.8.32.4.2.2.1.1.1.1" class="indexterm"></a>
  17         <code class="function">generate_series</code> ( <em class="parameter"><code>start</code></em> <code class="type">integer</code>, <em class="parameter"><code>stop</code></em> <code class="type">integer</code> [<span class="optional">, <em class="parameter"><code>step</code></em> <code class="type">integer</code> </span>] )
  18         → <code class="returnvalue">setof integer</code>
  19        </p>
  20        <p class="func_signature">
  21         <code class="function">generate_series</code> ( <em class="parameter"><code>start</code></em> <code class="type">bigint</code>, <em class="parameter"><code>stop</code></em> <code class="type">bigint</code> [<span class="optional">, <em class="parameter"><code>step</code></em> <code class="type">bigint</code> </span>] )
  22         → <code class="returnvalue">setof bigint</code>
  23        </p>
  24        <p class="func_signature">
  25         <code class="function">generate_series</code> ( <em class="parameter"><code>start</code></em> <code class="type">numeric</code>, <em class="parameter"><code>stop</code></em> <code class="type">numeric</code> [<span class="optional">, <em class="parameter"><code>step</code></em> <code class="type">numeric</code> </span>] )
  26         → <code class="returnvalue">setof numeric</code>
  27        </p>
  28        <p>
  29         Generates a series of values from <em class="parameter"><code>start</code></em>
  30         to <em class="parameter"><code>stop</code></em>, with a step size
  31         of <em class="parameter"><code>step</code></em>.  <em class="parameter"><code>step</code></em>
  32         defaults to 1.
  33        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
  34         <code class="function">generate_series</code> ( <em class="parameter"><code>start</code></em> <code class="type">timestamp</code>, <em class="parameter"><code>stop</code></em> <code class="type">timestamp</code>, <em class="parameter"><code>step</code></em> <code class="type">interval</code> )
  35         → <code class="returnvalue">setof timestamp</code>
  36        </p>
  37        <p class="func_signature">
  38         <code class="function">generate_series</code> ( <em class="parameter"><code>start</code></em> <code class="type">timestamp with time zone</code>, <em class="parameter"><code>stop</code></em> <code class="type">timestamp with time zone</code>, <em class="parameter"><code>step</code></em> <code class="type">interval</code> [<span class="optional">, <em class="parameter"><code>timezone</code></em> <code class="type">text</code> </span>] )
  39         → <code class="returnvalue">setof timestamp with time zone</code>
  40        </p>
  41        <p>
  42         Generates a series of values from <em class="parameter"><code>start</code></em>
  43         to <em class="parameter"><code>stop</code></em>, with a step size
  44         of <em class="parameter"><code>step</code></em>.
  45         In the timezone-aware form, times of day and daylight-savings
  46         adjustments are computed according to the time zone named by
  47         the <em class="parameter"><code>timezone</code></em> argument, or the current
  48         <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting if that is omitted.
  49        </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
  50    When <em class="parameter"><code>step</code></em> is positive, zero rows are returned if
  51    <em class="parameter"><code>start</code></em> is greater than <em class="parameter"><code>stop</code></em>.
  52    Conversely, when <em class="parameter"><code>step</code></em> is negative, zero rows are
  53    returned if <em class="parameter"><code>start</code></em> is less than <em class="parameter"><code>stop</code></em>.
  54    Zero rows are also returned if any input is <code class="literal">NULL</code>.
  55    It is an error
  56    for <em class="parameter"><code>step</code></em> to be zero. Some examples follow:
  57 </p><pre class="programlisting">
  58 SELECT * FROM generate_series(2,4);
  59  generate_series
  60 -----------------
  61                2
  62                3
  63                4
  64 (3 rows)
  65
  66 SELECT * FROM generate_series(5,1,-2);
  67  generate_series
  68 -----------------
  69                5
  70                3
  71                1
  72 (3 rows)
  73
  74 SELECT * FROM generate_series(4,3);
  75  generate_series
  76 -----------------
  77 (0 rows)
  78
  79 SELECT generate_series(1.1, 4, 1.3);
  80  generate_series
  81 -----------------
  82              1.1
  83              2.4
  84              3.7
  85 (3 rows)
  86
  87 -- this example relies on the date-plus-integer operator:
  88 SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
  89    dates
  90 ------------
  91  2004-02-05
  92  2004-02-12
  93  2004-02-19
  94 (3 rows)
  95
  96 SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
  97                               '2008-03-04 12:00', '10 hours');
  98    generate_series
  99 ---------------------
 100  2008-03-01 00:00:00
 101  2008-03-01 10:00:00
 102  2008-03-01 20:00:00
 103  2008-03-02 06:00:00
 104  2008-03-02 16:00:00
 105  2008-03-03 02:00:00
 106  2008-03-03 12:00:00
 107  2008-03-03 22:00:00
 108  2008-03-04 08:00:00
 109 (9 rows)
 110
 111 -- this example assumes that TimeZone is set to UTC; note the DST transition:
 112 SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
 113                               '2001-11-01 00:00 -05:00'::timestamptz,
 114                               '1 day'::interval, 'America/New_York');
 115     generate_series
 116 ------------------------
 117  2001-10-22 04:00:00+00
 118  2001-10-23 04:00:00+00
 119  2001-10-24 04:00:00+00
 120  2001-10-25 04:00:00+00
 121  2001-10-26 04:00:00+00
 122  2001-10-27 04:00:00+00
 123  2001-10-28 04:00:00+00
 124  2001-10-29 05:00:00+00
 125  2001-10-30 05:00:00+00
 126  2001-10-31 05:00:00+00
 127  2001-11-01 05:00:00+00
 128 (11 rows)
 129 </pre><p>
 130   </p><div class="table" id="FUNCTIONS-SRF-SUBSCRIPTS"><p class="title"><strong>Table 9.70. Subscript Generating Functions</strong></p><div class="table-contents"><table class="table" summary="Subscript Generating Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
 131         Function
 132        </p>
 133        <p>
 134         Description
 135        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
 136         <a id="id-1.5.8.32.6.2.2.1.1.1.1" class="indexterm"></a>
 137         <code class="function">generate_subscripts</code> ( <em class="parameter"><code>array</code></em> <code class="type">anyarray</code>, <em class="parameter"><code>dim</code></em> <code class="type">integer</code> )
 138         → <code class="returnvalue">setof integer</code>
 139        </p>
 140        <p>
 141         Generates a series comprising the valid subscripts of
 142         the <em class="parameter"><code>dim</code></em>'th dimension of the given array.
 143        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
 144         <code class="function">generate_subscripts</code> ( <em class="parameter"><code>array</code></em> <code class="type">anyarray</code>, <em class="parameter"><code>dim</code></em> <code class="type">integer</code>,  <em class="parameter"><code>reverse</code></em> <code class="type">boolean</code> )
 145         → <code class="returnvalue">setof integer</code>
 146        </p>
 147        <p>
 148         Generates a series comprising the valid subscripts of
 149         the <em class="parameter"><code>dim</code></em>'th dimension of the given array.
 150         When <em class="parameter"><code>reverse</code></em> is true, returns the series in
 151         reverse order.
 152        </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
 153    <code class="function">generate_subscripts</code> is a convenience function that generates
 154    the set of valid subscripts for the specified dimension of the given
 155    array.
 156    Zero rows are returned for arrays that do not have the requested dimension,
 157    or if any input is <code class="literal">NULL</code>.
 158    Some examples follow:
 159 </p><pre class="programlisting">
 160 -- basic usage:
 161 SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
 162  s
 163 ---
 164  1
 165  2
 166  3
 167  4
 168 (4 rows)
 169
 170 -- presenting an array, the subscript and the subscripted
 171 -- value requires a subquery:
 172 SELECT * FROM arrays;
 173          a
 174 --------------------
 175  {-1,-2}
 176  {100,200,300}
 177 (2 rows)
 178
 179 SELECT a AS array, s AS subscript, a[s] AS value
 180 FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
 181      array     | subscript | value
 182 ---------------+-----------+-------
 183  {-1,-2}       |         1 |    -1
 184  {-1,-2}       |         2 |    -2
 185  {100,200,300} |         1 |   100
 186  {100,200,300} |         2 |   200
 187  {100,200,300} |         3 |   300
 188 (5 rows)
 189
 190 -- unnest a 2D array:
 191 CREATE OR REPLACE FUNCTION unnest2(anyarray)
 192 RETURNS SETOF anyelement AS $$
 193 select $1[i][j]
 194    from generate_subscripts($1,1) g1(i),
 195         generate_subscripts($1,2) g2(j);
 196 $$ LANGUAGE sql IMMUTABLE;
 197 CREATE FUNCTION
 198 SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
 199  unnest2
 200 ---------
 201        1
 202        2
 203        3
 204        4
 205 (4 rows)
 206 </pre><p>
 207   </p><a id="id-1.5.8.32.8" class="indexterm"></a><p>
 208    When a function in the <code class="literal">FROM</code> clause is suffixed
 209    by <code class="literal">WITH ORDINALITY</code>, a <code class="type">bigint</code> column is
 210    appended to the function's output column(s), which starts from 1 and
 211    increments by 1 for each row of the function's output.
 212    This is most useful in the case of set returning
 213    functions such as <code class="function">unnest()</code>.
 214
 215 </p><pre class="programlisting">
 216 -- set returning function WITH ORDINALITY:
 217 SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
 218        ls        | n
 219 -----------------+----
 220  pg_serial       |  1
 221  pg_twophase     |  2
 222  postmaster.opts |  3
 223  pg_notify       |  4
 224  postgresql.conf |  5
 225  pg_tblspc       |  6
 226  logfile         |  7
 227  base            |  8
 228  postmaster.pid  |  9
 229  pg_ident.conf   | 10
 230  global          | 11
 231  pg_xact         | 12
 232  pg_snapshots    | 13
 233  pg_multixact    | 14
 234  PG_VERSION      | 15
 235  pg_wal          | 16
 236  pg_hba.conf     | 17
 237  pg_stat_tmp     | 18
 238  pg_subtrans     | 19
 239 (19 rows)
 240 </pre><p>
 241   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-comparisons.html" title="9.25. Row and Array Comparisons">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-info.html" title="9.27. System Information Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.25. Row and Array Comparisons </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.27. System Information Functions and Operators</td></tr></table></div></body></html>