<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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 Vsnapshot" /><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" /></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 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-info.html" title="9.25. System Information Functions">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>
This section describes functions that possibly return more than one row.
The most widely used functions in this class are series generating
functions, as detailed in <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SERIES" title="Table 9.58. Series Generating Functions">Table 9.58</a> and
<a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.59. Subscript Generating Functions">Table 9.59</a>. Other, more specialized
set-returning functions are described elsewhere in this manual.
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
set-returning functions.
</p><div class="table" id="FUNCTIONS-SRF-SERIES"><p class="title"><strong>Table 9.58. 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>
Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>
with a step size of one
</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>
Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>
with a step size of <em class="parameter"><code>step</code></em>
</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>
Generate a series of values, from <em class="parameter"><code>start</code></em> to <em class="parameter"><code>stop</code></em>
with a step size of <em class="parameter"><code>step</code></em>
</td></tr></tbody></table></div></div><br class="table-break" /><p>
When <em class="parameter"><code>step</code></em> is positive, zero rows are returned if
<em class="parameter"><code>start</code></em> is greater than <em class="parameter"><code>stop</code></em>.
Conversely, when <em class="parameter"><code>step</code></em> is negative, zero rows are
returned if <em class="parameter"><code>start</code></em> is less than <em class="parameter"><code>stop</code></em>.
Zero rows are also returned for <code class="literal">NULL</code> inputs. It is an error
for <em class="parameter"><code>step</code></em> to be zero. Some examples follow:
</p><pre class="programlisting">
SELECT * FROM generate_series(2,4);
generate_series
-----------------
2
3
4
(3 rows)
SELECT * FROM generate_series(5,1,-2);
generate_series
-----------------
5
3
1
(3 rows)
SELECT * FROM generate_series(4,3);
generate_series
-----------------
(0 rows)
SELECT generate_series(1.1, 4, 1.3);
generate_series
-----------------
1.1
2.4
3.7
(3 rows)
-- this example relies on the date-plus-integer operator
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)
</pre><p>
</p><div class="table" id="FUNCTIONS-SRF-SUBSCRIPTS"><p class="title"><strong>Table 9.59. 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>
Generate a series comprising the given array's subscripts.
</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>
Generate a series comprising the given array's subscripts. When
<em class="parameter"><code>reverse</code></em> is true, the series is returned in
reverse order.
</td></tr></tbody></table></div></div><br class="table-break" /><a id="id-1.5.8.29.8" class="indexterm"></a><p>
<code class="function">generate_subscripts</code> is a convenience function that generates
the set of valid subscripts for the specified dimension of the given
array.
Zero rows are returned for arrays that do not have the requested dimension,
or for NULL arrays (but valid subscripts are returned for NULL array
elements). Some examples follow:
</p><pre class="programlisting">
-- basic usage
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
(4 rows)
-- presenting an array, the subscript and the subscripted
-- value requires a subquery
SELECT * FROM arrays;
a
--------------------
{-1,-2}
{100,200,300}
(2 rows)
SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
array | subscript | value
---------------+-----------+-------
{-1,-2} | 1 | -1
{-1,-2} | 2 | -2
{100,200,300} | 1 | 100
{100,200,300} | 2 | 200
{100,200,300} | 3 | 300
(5 rows)
-- unnest a 2D array
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
unnest2
---------
1
2
3
4
(4 rows)
</pre><p>
</p><a id="id-1.5.8.29.10" class="indexterm"></a><p>
When a function in the <code class="literal">FROM</code> clause is suffixed
by <code class="literal">WITH ORDINALITY</code>, a <code class="type">bigint</code> column is
appended to the output which starts from 1 and increments by 1 for each row
of the function's output. This is most useful in the case of set returning
functions such as <code class="function">unnest()</code>.
</p><pre class="programlisting">
-- set returning function WITH ORDINALITY
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
ls | n
-----------------+----
pg_serial | 1
pg_twophase | 2
postmaster.opts | 3
pg_notify | 4
postgresql.conf | 5
pg_tblspc | 6
logfile | 7
base | 8
postmaster.pid | 9
pg_ident.conf | 10
global | 11
pg_xact | 12
pg_snapshots | 13
pg_multixact | 14
PG_VERSION | 15
pg_wal | 16
pg_hba.conf | 17
pg_stat_tmp | 18
pg_subtrans | 19
(19 rows)
</pre><p>
</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</td></tr></table></div></body></html>
