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 <br...@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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
