Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Watch 1
Star 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/functions-srf.html

174 lines
11KB
Raw Permalink Blame History 

  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.24. 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 V1.79.1" /><link rel="prev" href="functions-comparisons.html" title="9.23. Row and Array Comparisons" /><link rel="next" href="functions-info.html" title="9.25. System Information Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.24. Set Returning Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-comparisons.html" title="9.23. 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 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-info.html" title="9.25. System Information Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-SRF"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.24. Set Returning Functions</h2></div></div></div><a id="id-1.5.8.29.2" class="indexterm"></a><a id="id-1.5.8.29.3" 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.61. Series Generating Functions">Table 9.61</a> and

  6.    <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.62. Subscript Generating Functions">Table 9.62</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.61. Series Generating Functions</strong></p><div class="table-contents"><table class="table" summary="Series Generating Functions" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Argument Type</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">generate_series(<em class="parameter"><code>start</code></em>, <em class="parameter"><code>stop</code></em>)</code></code></td><td><code class="type">int</code>, <code class="type">bigint</code> or <code class="type">numeric</code></td><td><code class="type">setof int</code>, <code class="type">setof bigint</code>, or <code class="type">setof numeric</code> (same as argument type)</td><td>

  11.        Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>

  12.        with a step size of one

  13.       </td></tr><tr><td><code class="literal"><code class="function">generate_series(<em class="parameter"><code>start</code></em>, <em class="parameter"><code>stop</code></em>, <em class="parameter"><code>step</code></em>)</code></code></td><td><code class="type">int</code>, <code class="type">bigint</code> or <code class="type">numeric</code></td><td><code class="type">setof int</code>, <code class="type">setof bigint</code> or <code class="type">setof numeric</code> (same as argument type)</td><td>

  14.        Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>

  15.        with a step size of <em class="parameter"><code>step</code></em>

  16.       </td></tr><tr><td><code class="literal"><code class="function">generate_series(<em class="parameter"><code>start</code></em>, <em class="parameter"><code>stop</code></em>, <em class="parameter"><code>step</code></em> <code class="type">interval</code>)</code></code></td><td><code class="type">timestamp</code> or <code class="type">timestamp with time zone</code></td><td><code class="type">setof timestamp</code> or <code class="type">setof timestamp with time zone</code> (same as argument type)</td><td>

  17.        Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>

  18.        with a step size of <em class="parameter"><code>step</code></em>

  19.       </td></tr></tbody></table></div></div><br class="table-break" /><p>

  20.    When <em class="parameter"><code>step</code></em> is positive, zero rows are returned if

  21.    <em class="parameter"><code>start</code></em> is greater than <em class="parameter"><code>stop</code></em>.

  22.    Conversely, when <em class="parameter"><code>step</code></em> is negative, zero rows are

  23.    returned if <em class="parameter"><code>start</code></em> is less than <em class="parameter"><code>stop</code></em>.

  24.    Zero rows are also returned for <code class="literal">NULL</code> inputs. It is an error

  25.    for <em class="parameter"><code>step</code></em> to be zero. Some examples follow:

  26. </p><pre class="programlisting">

  27. SELECT * FROM generate_series(2,4);

  28.  generate_series

  29. -----------------

  30.                2

  31.                3

  32.                4

  33. (3 rows)

  34. 

  35. SELECT * FROM generate_series(5,1,-2);

  36.  generate_series

  37. -----------------

  38.                5

  39.                3

  40.                1

  41. (3 rows)

  42. 

  43. SELECT * FROM generate_series(4,3);

  44.  generate_series

  45. -----------------

  46. (0 rows)

  47. 

  48. SELECT generate_series(1.1, 4, 1.3);

  49.  generate_series 

  50. -----------------

  51.              1.1

  52.              2.4

  53.              3.7

  54. (3 rows)

  55. 

  56. -- this example relies on the date-plus-integer operator

  57. SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);

  58.    dates

  59. ------------

  60.  2004-02-05

  61.  2004-02-12

  62.  2004-02-19

  63. (3 rows)

  64. 

  65. SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,

  66.                               '2008-03-04 12:00', '10 hours');

  67.    generate_series   

  68. ---------------------

  69.  2008-03-01 00:00:00

  70.  2008-03-01 10:00:00

  71.  2008-03-01 20:00:00

  72.  2008-03-02 06:00:00

  73.  2008-03-02 16:00:00

  74.  2008-03-03 02:00:00

  75.  2008-03-03 12:00:00

  76.  2008-03-03 22:00:00

  77.  2008-03-04 08:00:00

  78. (9 rows)

  79. </pre><p>

  80.   </p><div class="table" id="FUNCTIONS-SRF-SUBSCRIPTS"><p class="title"><strong>Table 9.62. Subscript Generating Functions</strong></p><div class="table-contents"><table class="table" summary="Subscript Generating Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">generate_subscripts(<em class="parameter"><code>array anyarray</code></em>, <em class="parameter"><code>dim int</code></em>)</code></code></td><td><code class="type">setof int</code></td><td>

  81.        Generate a series comprising the given array's subscripts.

  82.       </td></tr><tr><td><code class="literal"><code class="function">generate_subscripts(<em class="parameter"><code>array anyarray</code></em>, <em class="parameter"><code>dim int</code></em>, <em class="parameter"><code>reverse boolean</code></em>)</code></code></td><td><code class="type">setof int</code></td><td>

  83.        Generate a series comprising the given array's subscripts. When

  84.        <em class="parameter"><code>reverse</code></em> is true, the series is returned in

  85.        reverse order.

  86.       </td></tr></tbody></table></div></div><br class="table-break" /><a id="id-1.5.8.29.8" class="indexterm"></a><p>

  87.    <code class="function">generate_subscripts</code> is a convenience function that generates

  88.    the set of valid subscripts for the specified dimension of the given

  89.    array.

  90.    Zero rows are returned for arrays that do not have the requested dimension,

  91.    or for NULL arrays (but valid subscripts are returned for NULL array

  92.    elements).  Some examples follow:

  93. </p><pre class="programlisting">

  94. -- basic usage

  95. SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;

  96.  s 

  97. ---

  98.  1

  99.  2

  100.  3

  101.  4

  102. (4 rows)

  103. 

  104. -- presenting an array, the subscript and the subscripted

  105. -- value requires a subquery

  106. SELECT * FROM arrays;

  107.          a          

  108. --------------------

  109.  {-1,-2}

  110.  {100,200,300}

  111. (2 rows)

  112. 

  113. SELECT a AS array, s AS subscript, a[s] AS value

  114. FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;

  115.      array     | subscript | value

  116. ---------------+-----------+-------

  117.  {-1,-2}       |         1 |    -1

  118.  {-1,-2}       |         2 |    -2

  119.  {100,200,300} |         1 |   100

  120.  {100,200,300} |         2 |   200

  121.  {100,200,300} |         3 |   300

  122. (5 rows)

  123. 

  124. -- unnest a 2D array

  125. CREATE OR REPLACE FUNCTION unnest2(anyarray)

  126. RETURNS SETOF anyelement AS $$

  127. select $1[i][j]

  128.    from generate_subscripts($1,1) g1(i),

  129.         generate_subscripts($1,2) g2(j);

  130. $$ LANGUAGE sql IMMUTABLE;

  131. CREATE FUNCTION

  132. SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);

  133.  unnest2 

  134. ---------

  135.        1

  136.        2

  137.        3

  138.        4

  139. (4 rows)

  140. </pre><p>

  141.   </p><a id="id-1.5.8.29.10" class="indexterm"></a><p>

  142.    When a function in the <code class="literal">FROM</code> clause is suffixed

  143.    by <code class="literal">WITH ORDINALITY</code>, a <code class="type">bigint</code> column is

  144.    appended to the output which starts from 1 and increments by 1 for each row

  145.    of the function's output.  This is most useful in the case of set returning

  146.    functions such as <code class="function">unnest()</code>.

  147. 

  148. </p><pre class="programlisting">

  149. -- set returning function WITH ORDINALITY

  150. SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);

  151.        ls        | n

  152. -----------------+----

  153.  pg_serial       |  1

  154.  pg_twophase     |  2

  155.  postmaster.opts |  3

  156.  pg_notify       |  4

  157.  postgresql.conf |  5

  158.  pg_tblspc       |  6

  159.  logfile         |  7

  160.  base            |  8

  161.  postmaster.pid  |  9

  162.  pg_ident.conf   | 10

  163.  global          | 11

  164.  pg_xact         | 12

  165.  pg_snapshots    | 13

  166.  pg_multixact    | 14

  167.  PG_VERSION      | 15

  168.  pg_wal          | 16

  169.  pg_hba.conf     | 17

  170.  pg_stat_tmp     | 18

  171.  pg_subtrans     | 19

  172. (19 rows)

  173. </pre><p>

  174.   </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-info.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.23. Row and Array Comparisons </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.25. System Information Functions and Operators</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1