Subject: [PATCH] Explain generate_subscripts() more clearly

From: Tim Landscheidt <[email-masked]>
Date: Thu, 18 Mar 2010 15:52:31 +0000

Hi,

the current documentation on generate_subscripts() uses a
"rectangular" example, i. e. where both arrays in question
have the same length (that is furthermore equal to the num-
ber of arrays, a "square" example so to speak :-)). To point
out that generate_subscripts () can be used for more complex
cases as well, please find attached patch.

Tim
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3419440..2677db9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11427,21 +11427,22 @@ select generate_subscripts('{NULL,1,NULL,2}'::int[], 1) as s;
 -- presenting an array, the subscript and the subscripted
 -- value requires a subquery
 select * from arrays;
-         a          
---------------------
+       a
+---------------
  {-1,-2}
- {100,200}
-(2 rows)
+ {100,200,300}
+(2 Zeilen)
 
 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)
+     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 Zeilen)
 
 -- unnest a 2D array
 create or replace function unnest2(anyarray)

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 2 Jun 2010 22:07:02 -0400 (EDT)

Tim Landscheidt wrote:
> "rectangular" example, i. e. where both arrays in question have
> the same length (that is furthermore equal to the num- ber of
> arrays, a "square" example so to speak :-)). To point out that
> generate_subscripts () can be used for more complex cases as
> well, please find attached patch.
> 
> 

> Content-Description: Patch to illustrate generate_subscripts ()'s
> behaviour more clearly.

Modified, applied patch attached.  Thanks.

--
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.514
diff -c -c -r1.514 func.sgml
*** doc/src/sgml/func.sgml	3 Jun 2010 01:34:02 -0000	1.514
--- doc/src/sgml/func.sgml	3 Jun 2010 01:59:31 -0000
***************
*** 11419,11425 ****
     elements).  Some examples follow:
  <programlisting>
  -- basic usage
! select generate_subscripts('{NULL,1,NULL,2}'::int[], 1) as s;
   s 
  ---
   1
--- 11419,11425 ----
     elements).  Some examples follow:
  <programlisting>
  -- basic usage
! SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
   s 
  ---
   1
***************
*** 11430,11461 ****
  
! 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
--- 11430,11462 ----
  
! 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
! postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
   unnest2 
  ---------
         1