public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Explain generate_subscripts() more clearly
2+ messages / 2 participants
[nested] [flat]
* [PATCH] Explain generate_subscripts() more clearly
@ 2010-03-18 15:52 Tim Landscheidt <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tim Landscheidt @ 2010-03-18 15:52 UTC (permalink / raw)
To: pgsql-docs
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
Attachments:
[text/x-patch] generate-subscripts-doc.patch (1.1K, 2-generate-subscripts-doc.patch)
download | inline diff:
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PATCH] Explain generate_subscripts() more clearly
@ 2010-06-03 02:07 Bruce Momjian <[email protected]>
parent: Tim Landscheidt <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2010-06-03 02:07 UTC (permalink / raw)
To: Tim Landscheidt <[email protected]>; +Cc: 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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachments:
[text/x-diff] /rtmp/diff (2.5K, 2-%2Frtmp%2Fdiff)
download | inline 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-06-03 02:07 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-18 15:52 [PATCH] Explain generate_subscripts() more clearly Tim Landscheidt <[email protected]>
2010-06-03 02:07 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox