<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Set Returning Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="Row and Array Comparisons"
HREF="functions-comparisons.html"><LINK
REL="NEXT"
TITLE="System Information Functions"
HREF="functions-info.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-05-31T23:30:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4.12 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-comparisons.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-info.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-SRF"
>9.22. Set Returning Functions</A
></H1
><A
NAME="AEN16661"
></A
><A
NAME="AEN16664"
></A
><P
> This section describes functions that possibly return more than one row.
Currently the only functions in this class are series generating functions,
as detailed in <A
HREF="functions-srf.html#FUNCTIONS-SRF-SERIES"
>Table 9-45</A
> and
<A
HREF="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS"
>Table 9-46</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-SRF-SERIES"
></A
><P
><B
>Table 9-45. Series Generating Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Argument Type</TH
><TH
>Return Type</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>generate_series</CODE
>(<TT
CLASS="PARAMETER"
>start</TT
>, <TT
CLASS="PARAMETER"
>stop</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>int</TT
> or <TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
><TT
CLASS="TYPE"
>setof int</TT
> or <TT
CLASS="TYPE"
>setof bigint</TT
> (same as argument type)</TD
><TD
> Generate a series of values, from <TT
CLASS="PARAMETER"
>start</TT
> to <TT
CLASS="PARAMETER"
>stop</TT
>
with a step size of one
</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>generate_series</CODE
>(<TT
CLASS="PARAMETER"
>start</TT
>, <TT
CLASS="PARAMETER"
>stop</TT
>, <TT
CLASS="PARAMETER"
>step</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>int</TT
> or <TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
><TT
CLASS="TYPE"
>setof int</TT
> or <TT
CLASS="TYPE"
>setof bigint</TT
> (same as argument type)</TD
><TD
> Generate a series of values, from <TT
CLASS="PARAMETER"
>start</TT
> to <TT
CLASS="PARAMETER"
>stop</TT
>
with a step size of <TT
CLASS="PARAMETER"
>step</TT
>
</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>generate_series</CODE
>(<TT
CLASS="PARAMETER"
>start</TT
>, <TT
CLASS="PARAMETER"
>stop</TT
>, <TT
CLASS="PARAMETER"
>step</TT
> <TT
CLASS="TYPE"
>interval</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>timestamp</TT
> or <TT
CLASS="TYPE"
>timestamp with time zone</TT
></TD
><TD
><TT
CLASS="TYPE"
>setof timestamp</TT
> or <TT
CLASS="TYPE"
>setof timestamp with time zone</TT
> (same as argument type)</TD
><TD
> Generate a series of values, from <TT
CLASS="PARAMETER"
>start</TT
> to <TT
CLASS="PARAMETER"
>stop</TT
>
with a step size of <TT
CLASS="PARAMETER"
>step</TT
>
</TD
></TR
></TBODY
></TABLE
></DIV
><P
> When <TT
CLASS="PARAMETER"
>step</TT
> is positive, zero rows are returned if
<TT
CLASS="PARAMETER"
>start</TT
> is greater than <TT
CLASS="PARAMETER"
>stop</TT
>.
Conversely, when <TT
CLASS="PARAMETER"
>step</TT
> is negative, zero rows are
returned if <TT
CLASS="PARAMETER"
>start</TT
> is less than <TT
CLASS="PARAMETER"
>stop</TT
>.
Zero rows are also returned for <TT
CLASS="LITERAL"
>NULL</TT
> inputs. It is an error
for <TT
CLASS="PARAMETER"
>step</TT
> 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)
-- 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"
><A
NAME="FUNCTIONS-SRF-SUBSCRIPTS"
></A
><P
><B
>Table 9-46. Subscript Generating Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Return Type</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>generate_subscripts</CODE
>(<TT
CLASS="PARAMETER"
>array anyarray</TT
>, <TT
CLASS="PARAMETER"
>dim int</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>setof int</TT
></TD
><TD
> Generate a series comprising the given array's subscripts.
</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>generate_subscripts</CODE
>(<TT
CLASS="PARAMETER"
>array anyarray</TT
>, <TT
CLASS="PARAMETER"
>dim int</TT
>, <TT
CLASS="PARAMETER"
>reverse boolean</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>setof int</TT
></TD
><TD
> Generate a series comprising the given array's subscripts. When
<TT
CLASS="PARAMETER"
>reverse</TT
> is true, the series is returned in
reverse order.
</TD
></TR
></TBODY
></TABLE
></DIV
><A
NAME="AEN16768"
></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}
(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} | 1 | 100
{100,200} | 2 | 200
(4 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
postgres=# select * from unnest2(array[[1,2],[3,4]]);
unnest2
---------
1
2
3
4
(4 rows)</PRE
><P>
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-comparisons.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-info.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Row and Array Comparisons</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>System Information Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
distrib
>
Mandriva
>
current
>
i586
>
media
>
main-updates
>
by-pkgid
>
fc62ce67f262cdcd253dc7f849ce3223
>
files
>
224
