path: root/doc/src/sgml/func/func-srf.sgml

 <sect1 id="functions-srf">
  <title>Set Returning Functions</title>

  <indexterm zone="functions-srf">
   <primary>set returning functions</primary>
   <secondary>functions</secondary>
  </indexterm>

  <para>
   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 <xref linkend="functions-srf-series"/> and
   <xref linkend="functions-srf-subscripts"/>.  Other, more specialized
   set-returning functions are described elsewhere in this manual.
   See <xref linkend="queries-tablefunctions"/> for ways to combine multiple
   set-returning functions.
  </para>

   <table id="functions-srf-series">
    <title>Series Generating Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>generate_series</primary>
        </indexterm>
        <function>generate_series</function> ( <parameter>start</parameter> <type>integer</type>, <parameter>stop</parameter> <type>integer</type> <optional>, <parameter>step</parameter> <type>integer</type> </optional> )
        <returnvalue>setof integer</returnvalue>
       </para>
       <para role="func_signature">
        <function>generate_series</function> ( <parameter>start</parameter> <type>bigint</type>, <parameter>stop</parameter> <type>bigint</type> <optional>, <parameter>step</parameter> <type>bigint</type> </optional> )
        <returnvalue>setof bigint</returnvalue>
       </para>
       <para role="func_signature">
        <function>generate_series</function> ( <parameter>start</parameter> <type>numeric</type>, <parameter>stop</parameter> <type>numeric</type> <optional>, <parameter>step</parameter> <type>numeric</type> </optional> )
        <returnvalue>setof numeric</returnvalue>
       </para>
       <para>
        Generates a series of values from <parameter>start</parameter>
        to <parameter>stop</parameter>, with a step size
        of <parameter>step</parameter>.  <parameter>step</parameter>
        defaults to 1.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>generate_series</function> ( <parameter>start</parameter> <type>timestamp</type>, <parameter>stop</parameter> <type>timestamp</type>, <parameter>step</parameter> <type>interval</type> )
        <returnvalue>setof timestamp</returnvalue>
       </para>
       <para role="func_signature">
        <function>generate_series</function> ( <parameter>start</parameter> <type>timestamp with time zone</type>, <parameter>stop</parameter> <type>timestamp with time zone</type>, <parameter>step</parameter> <type>interval</type> <optional>, <parameter>timezone</parameter> <type>text</type> </optional> )
        <returnvalue>setof timestamp with time zone</returnvalue>
       </para>
       <para>
        Generates a series of values from <parameter>start</parameter>
        to <parameter>stop</parameter>, with a step size
        of <parameter>step</parameter>.
        In the timezone-aware form, times of day and daylight-savings
        adjustments are computed according to the time zone named by
        the <parameter>timezone</parameter> argument, or the current
        <xref linkend="guc-timezone"/> setting if that is omitted.
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   When <parameter>step</parameter> is positive, zero rows are returned if
   <parameter>start</parameter> is greater than <parameter>stop</parameter>.
   Conversely, when <parameter>step</parameter> is negative, zero rows are
   returned if <parameter>start</parameter> is less than <parameter>stop</parameter>.
   Zero rows are also returned if any input is <literal>NULL</literal>.
   It is an error
   for <parameter>step</parameter> to be zero. Some examples follow:
<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)
</programlisting>
  </para>

   <table id="functions-srf-subscripts">
    <title>Subscript Generating Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>generate_subscripts</primary>
        </indexterm>
        <function>generate_subscripts</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>dim</parameter> <type>integer</type> )
        <returnvalue>setof integer</returnvalue>
       </para>
       <para>
        Generates a series comprising the valid subscripts of
        the <parameter>dim</parameter>'th dimension of the given array.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>generate_subscripts</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>dim</parameter> <type>integer</type>,  <parameter>reverse</parameter> <type>boolean</type> )
        <returnvalue>setof integer</returnvalue>
       </para>
       <para>
        Generates a series comprising the valid subscripts of
        the <parameter>dim</parameter>'th dimension of the given array.
        When <parameter>reverse</parameter> is true, returns the series in
        reverse order.
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   <function>generate_subscripts</function> 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 <literal>NULL</literal>.
   Some examples follow:
<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)
</programlisting>
  </para>

  <indexterm>
   <primary>ordinality</primary>
  </indexterm>

  <para>
   When a function in the <literal>FROM</literal> clause is suffixed
   by <literal>WITH ORDINALITY</literal>, a <type>bigint</type> 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 <function>unnest()</function>.

<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)
</programlisting>
  </para>

 </sect1>