On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote:
> On Sat, Nov 20, 2010 at 03:48, <[email protected]> wrote:
> > Note the standard also supports unnesting multiple arrays concurrently, the
> > rule for handling arrays with different lengths is 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 | Here
2 | 2 | is | is
3 | 4 | some | yet
4 | 8 | odd | more
5 | | text | text
6 | | of |
7 | | a |
8 | | different |
9 | | length |
(9 rows)
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
