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.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