Re: [PATCH] Explain generate_subscripts() more clearly - Mailing list pgsql-docs

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. +
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