Re: [PATCH] Explain generate_subscripts() more clearly
От | Bruce Momjian |
---|---|
Тема | Re: [PATCH] Explain generate_subscripts() more clearly |
Дата | |
Msg-id | 201006030207.o53272J18248@momjian.us обсуждение исходный текст |
Ответ на | [PATCH] Explain generate_subscripts() more clearly (Tim Landscheidt <tim@tim-landscheidt.de>) |
Список | 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
В списке pgsql-docs по дате отправления:
Предыдущее
От: Joshua TolleyДата:
Сообщение: Re: commit_delay and commit_siblings -- restart or reload?
Следующее
От: Nikolay SamokhvalovДата:
Сообщение: More clearly on XML namespaces inside xpath function