Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF)

On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote:
> On Sat, Nov 20, 2010 at 03:48, <Caleb(dot)Welton(at)emc(dot)com> 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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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