From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201006030207.o53272J18248@momjian.us>
Subject: Re: [PATCH] Explain generate_subscripts() more clearly
In-Reply-To: <m3d3z1eif4.fsf@passepartout.tim-landscheidt.de>
To: Tim Landscheidt <tim@tim-landscheidt.de>
Date: Wed, 2 Jun 2010 22:07:02 -0400 (EDT)
CC: pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1275530822-22459-0_"
Content-Transfer-Encoding: 7bit


--ELM1275530822-22459-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Tim Landscheidt wrote:
> 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.
> 
> 

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

Modified, applied patch attached.  Thanks.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

--ELM1275530822-22459-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/diff"

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 ****
  
  -- 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
--- 11430,11462 ----
  
  -- 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
! postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
   unnest2 
  ---------
         1

--ELM1275530822-22459-0_--