diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/array.sgml | 17 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 98 |
2 files changed, 112 insertions, 3 deletions
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 87df7e556a2..b0d6e19abf7 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.65 2008/04/27 04:33:27 alvherre Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.66 2008/04/28 14:48:57 alvherre Exp $ --> <sect1 id="arrays"> <title>Arrays</title> @@ -542,6 +542,21 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); </para> + <para> + Alternatively, the <function>generate_subscripts</> function can be used. + For example: + +<programlisting> +SELECT * FROM + (SELECT pay_by_quarter, + generate_subscripts(pay_by_quarter, 1) AS s + FROM sal_emp) AS foo + WHERE pay_by_quarter[s] = 10000; +</programlisting> + + This function is described in <xref linkend="functions-srf-subscripts">. + </para> + <tip> <para> Arrays are not sets; searching for specific array elements diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7e120bc8621..d0171798675 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.433 2008/04/17 20:56:41 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.434 2008/04/28 14:48:57 alvherre Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -10613,7 +10613,8 @@ AND <para> 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 <xref linkend="functions-srf-series">. + as detailed in <xref linkend="functions-srf-series"> and + <xref linkend="functions-srf-subscripts">. </para> <table id="functions-srf-series"> @@ -10691,6 +10692,99 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a); (3 rows) </programlisting> </para> + + <table id="functions-srf-subscripts"> + + <indexterm> + <primary>generate_subscripts</primary> + </indexterm> + + <title>Subscripts Generating Functions</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Function</entry> + <entry>Return Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>)</literal></entry> + <entry><type>setof int</type></entry> + <entry> + Generate a series comprising the given array's subscripts. + </entry> + </row> + + <row> + <entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry> + <entry><type>setof int</type></entry> + <entry> + Generate a series comprising the given array's subscripts. When + <parameter>reverse</parameter> is true, the series is returned in + reverse order. + </entry> + </row> + + </tbody> + </tgroup> + </table> + + <para> + 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: +<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) +</programlisting> + </para> + </sect1> <sect1 id="functions-info"> |