From 1fcb977a13d70f8746ec86488fd9afc0569e7784 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 28 Apr 2008 14:48:58 +0000
Subject: Add generate_subscripts, a series-generation function which generates
 an array's subscripts.

Pavel Stehule, some editorialization by me.
---
 doc/src/sgml/array.sgml | 17 ++++++++-
 doc/src/sgml/func.sgml  | 98 ++++++++++++++++++++++++++++++++++++++++++++++++-
 2 files changed, 112 insertions(+), 3 deletions(-)

(limited to 'doc/src')

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">
-- 
cgit v1.2.3