1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
<?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.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.2 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>
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.65. Series Generating Functions">Table 9.65</a> and
<a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.66. Subscript Generating Functions">Table 9.66</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.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">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.31.4.2.2.1.1.1.1" class="indexterm"></a>
<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>] )
→ <code class="returnvalue">setof integer</code>
</p>
<p class="func_signature">
<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>] )
→ <code class="returnvalue">setof bigint</code>
</p>
<p class="func_signature">
<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>] )
→ <code class="returnvalue">setof numeric</code>
</p>
<p>
Generates 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>. <em class="parameter"><code>step</code></em>
defaults to 1.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<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> )
→ <code class="returnvalue">setof timestamp</code>
</p>
<p class="func_signature">
<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>] )
→ <code class="returnvalue">setof timestamp with time zone</code>
</p>
<p>
Generates 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>.
In the timezone-aware form, times of day and daylight-savings
adjustments are computed according to the time zone named by
the <em class="parameter"><code>timezone</code></em> argument, or the current
<a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting if that is omitted.
</p></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 if any input is <code class="literal">NULL</code>.
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)
-- this example assumes that TimeZone is set to UTC; note the DST transition:
SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
'2001-11-01 00:00 -05:00'::timestamptz,
'1 day'::interval, 'America/New_York');
generate_series
------------------------
2001-10-22 04:00:00+00
2001-10-23 04:00:00+00
2001-10-24 04:00:00+00
2001-10-25 04:00:00+00
2001-10-26 04:00:00+00
2001-10-27 04:00:00+00
2001-10-28 04:00:00+00
2001-10-29 05:00:00+00
2001-10-30 05:00:00+00
2001-10-31 05:00:00+00
2001-11-01 05:00:00+00
(11 rows)
</pre><p>
</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">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.31.6.2.2.1.1.1.1" class="indexterm"></a>
<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> )
→ <code class="returnvalue">setof integer</code>
</p>
<p>
Generates a series comprising the valid subscripts of
the <em class="parameter"><code>dim</code></em>'th dimension of the given array.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<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> )
→ <code class="returnvalue">setof integer</code>
</p>
<p>
Generates a series comprising the valid subscripts of
the <em class="parameter"><code>dim</code></em>'th dimension of the given array.
When <em class="parameter"><code>reverse</code></em> is true, returns the series in
reverse order.
</p></td></tr></tbody></table></div></div><br class="table-break" /><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 if any input is <code class="literal">NULL</code>.
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.31.8" 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 function's output column(s), 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" 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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.26. System Information Functions and Operators</td></tr></table></div></body></html>
|
