On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote:
> On Sat, Nov 20, 2010 at 03:48, <Caleb.Welton@emc.com> wrote:
> > Note the standard also supports unnesting multiple arrays concurrently, the rule for handling arrays with different
lengthsis to use null padding of the shorter array.
> >
> > UNNEST( ARRAY[5,2,3,4],
> > ARRAY['hello', 'world'] )
> > WITH ORDINALITY AS t(a,b,i);
>
> Hmmm, that means we cannot support multi-array unnest() with our
> generic aggregate functions. The function prototype might be like
> below, but we don't support such definition.
>
> unnest(anyarray1, anyarray2, ...,
> OUT anyelement1, OUT anyelement2, ...)
> RETURNS SETOF record
>
> So, we would need a special representation for multi-array unnest().
Using bits we already have, I came up with a way to do the things
UNNEST(multiple, arrays, here) WITH ORDINALITY does. At least in
theory, this is a matter of silently engaging the rewrite rule system:
\set foo ARRAY[1,2,4,8]
\set bar ARRAY['Here','is','some','odd','text','of','a','different','length']
\set baz ARRAY['Here','is','yet','more','text']
WITH x AS ( SELECT row_number() OVER () i, foo FROM UNNEST(:foo) foo
),
y AS ( SELECT row_number() OVER () i, bar FROM UNNEST(:bar) bar
),
z AS ( SELECT row_number() OVER () i, baz FROM UNNEST(:baz) baz
)
SELECT * FROM x FULL JOIN y USING(i) FULL JOIN z USING(i);
a i | foo | bar | baz
---+-----+-----------+------1 | 1 | Here | Here2 | 2 | is | is3 | 4 | some | yet4 | 8 | odd
| more5 | | text | text6 | | of | 7 | | a | 8 | | different | 9 | | length
|
(9 rows)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate