From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> |
Cc: | Caleb(dot)Welton(at)emc(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) |
Date: | 2010-11-21 02:25:09 |
Message-ID: | 20101121022509.GA31284@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-11-21 03:35:57 | Re: Fwd: patch: format function - fixed oid |
Previous Message | Jeff Janes | 2010-11-21 02:11:40 | Re: Spread checkpoint sync |