diff options
Diffstat (limited to 'doc/src/sgml/func/func-srf.sgml')
| -rw-r--r-- | doc/src/sgml/func/func-srf.sgml | 306 |
1 files changed, 306 insertions, 0 deletions
diff --git a/doc/src/sgml/func/func-srf.sgml b/doc/src/sgml/func/func-srf.sgml new file mode 100644 index 00000000000..eafc961c9f9 --- /dev/null +++ b/doc/src/sgml/func/func-srf.sgml @@ -0,0 +1,306 @@ + <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> |