codekingpro/portable-devtools
115k
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.25. 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.24. Row and Array Comparisons" /><link rel="next" href="functions-info.html" title="9.26. 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.25. Set Returning Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-comparisons.html" title="9.24. 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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-info.html" title="9.26. 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.25. Set Returning Functions <a href="#FUNCTIONS-SRF" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.31.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 generating5 functions, as detailed in <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SERIES" title="Table 9.65. Series Generating Functions">Table 9.65</a> and6 <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.66. Subscript Generating Functions">Table 9.66</a>. Other, more specialized7 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 multiple9 set-returning functions.10 </p><div class="table" id="FUNCTIONS-SRF-SERIES"><p class="title"><strong>Table 9.65. 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 Function12 </p>13 <p>14 Description15 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">16 <a id="id-1.5.8.31.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 size31 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 size44 of <em class="parameter"><code>step</code></em>.45 In the timezone-aware form, times of day and daylight-savings46 adjustments are computed according to the time zone named by47 the <em class="parameter"><code>timezone</code></em> argument, or the current48 <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 if51 <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 are53 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 error56 for <em class="parameter"><code>step</code></em> to be zero. Some examples follow:57</p><pre class="programlisting">58SELECT * FROM generate_series(2,4);59 generate_series60-----------------61 262 363 464(3 rows)65 66SELECT * FROM generate_series(5,1,-2);67 generate_series68-----------------69 570 371 172(3 rows)73 74SELECT * FROM generate_series(4,3);75 generate_series76-----------------77(0 rows)78 79SELECT generate_series(1.1, 4, 1.3);80 generate_series81-----------------82 1.183 2.484 3.785(3 rows)86 87-- this example relies on the date-plus-integer operator:88SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);89 dates90------------91 2004-02-0592 2004-02-1293 2004-02-1994(3 rows)95 96SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,97 '2008-03-04 12:00', '10 hours');98 generate_series99---------------------100 2008-03-01 00:00:00101 2008-03-01 10:00:00102 2008-03-01 20:00:00103 2008-03-02 06:00:00104 2008-03-02 16:00:00105 2008-03-03 02:00:00106 2008-03-03 12:00:00107 2008-03-03 22:00:00108 2008-03-04 08:00:00109(9 rows)110 111-- this example assumes that TimeZone is set to UTC; note the DST transition:112SELECT * 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_series116------------------------117 2001-10-22 04:00:00+00118 2001-10-23 04:00:00+00119 2001-10-24 04:00:00+00120 2001-10-25 04:00:00+00121 2001-10-26 04:00:00+00122 2001-10-27 04:00:00+00123 2001-10-28 04:00:00+00124 2001-10-29 05:00:00+00125 2001-10-30 05:00:00+00126 2001-10-31 05:00:00+00127 2001-11-01 05:00:00+00128(11 rows)129</pre><p>130 </p><div class="table" id="FUNCTIONS-SRF-SUBSCRIPTS"><p class="title"><strong>Table 9.66. 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 Function132 </p>133 <p>134 Description135 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">136 <a id="id-1.5.8.31.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 of142 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 of149 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 in151 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 generates154 the set of valid subscripts for the specified dimension of the given155 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:161SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;162 s163---164 1165 2166 3167 4168(4 rows)169 170-- presenting an array, the subscript and the subscripted171-- value requires a subquery:172SELECT * FROM arrays;173 a174--------------------175 {-1,-2}176 {100,200,300}177(2 rows)178 179SELECT a AS array, s AS subscript, a[s] AS value180FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;181 array | subscript | value182---------------+-----------+-------183 {-1,-2} | 1 | -1184 {-1,-2} | 2 | -2185 {100,200,300} | 1 | 100186 {100,200,300} | 2 | 200187 {100,200,300} | 3 | 300188(5 rows)189 190-- unnest a 2D array:191CREATE OR REPLACE FUNCTION unnest2(anyarray)192RETURNS SETOF anyelement AS $$193select $1[i][j]194 from generate_subscripts($1,1) g1(i),195 generate_subscripts($1,2) g2(j);196$$ LANGUAGE sql IMMUTABLE;197CREATE FUNCTION198SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);199 unnest2200---------201 1202 2203 3204 4205(4 rows)206</pre><p>207 </p><a id="id-1.5.8.31.8" class="indexterm"></a><p>208 When a function in the <code class="literal">FROM</code> clause is suffixed209 by <code class="literal">WITH ORDINALITY</code>, a <code class="type">bigint</code> column is210 appended to the function's output column(s), which starts from 1 and211 increments by 1 for each row of the function's output.212 This is most useful in the case of set returning213 functions such as <code class="function">unnest()</code>.214 215</p><pre class="programlisting">216-- set returning function WITH ORDINALITY:217SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);218 ls | n219-----------------+----220 pg_serial | 1221 pg_twophase | 2222 postmaster.opts | 3223 pg_notify | 4224 postgresql.conf | 5225 pg_tblspc | 6226 logfile | 7227 base | 8228 postmaster.pid | 9229 pg_ident.conf | 10230 global | 11231 pg_xact | 12232 pg_snapshots | 13233 pg_multixact | 14234 PG_VERSION | 15235 pg_wal | 16236 pg_hba.conf | 17237 pg_stat_tmp | 18238 pg_subtrans | 19239(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.24. 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.26. System Information Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.24. Row and Array Comparisons </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.26. System Information Functions and Operators</td></tr></table></div></body></html>