From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) |
Date: | 2010-11-19 07:48:16 |
Message-ID: | 20101119074816.GA14074@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote:
> On Fri, Nov 19, 2010 at 08:33, David Fetter <david(at)fetter(dot)org> wrote:
> > In order to get WITH ORDINALITY, would it be better to change
> > gram.y to account for both WITH ORDINALITY and without, or just
> > for the WITH ORDINALITY case?
>
> We probably need to change gram.y and make UNNEST to be
> COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the
> existing unnest() function, and UNNEST() WITH ORDINALITY will call
> unnest_with_ordinality().
Thanks for sketching that out :)
> BTW, what will we return for arrays with 2 or more dimensions?
At the moment, per the SQL standard, UNNEST without the WITH
ORDINALITY clause flattens all dimensions.
SELECT * FROM UNNEST(ARRAY[[1,2],[3,4]]);
unnest
--------
1
2
3
4
(4 rows)
Unless we want to do something super wacky and contrary to the SQL
standard, UNNEST(array) WITH ORDINALITY should do the same.
> There are no confusion in your two arguments version:
> > UNNEST(anyarray, number_of_dimensions_to_unnest)
> but we will also support one argument version. Array indexes will
> be composite numbers in the cases. The possible design would be just
> return sequential serial numbers of the values -- the following two
> queries return the same results:
>
> - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i)
> - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v)
Yes, that's what the standard says. Possible less-than-total
unrolling schemes include:
- Flatten specified number of initial dimensions into one list, e.g.
turn UNNEST(array_3d, 2) into SETOF(array_1d) with one column of
ORDINALITY
- Flatten similarly, but have an ORDINALITY column for each flattened
dimension.
- More exotic schemes, such as UNNEST(array_3d, [1,3]), with either of
the two methods above.
And of course the all-important:
- Other possibilities I haven't thought of :)
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 | Pavel Stehule | 2010-11-19 07:54:07 | Fwd: patch: format function - fixed oid |
Previous Message | Pavel Stehule | 2010-11-19 06:40:57 | Re: final patch - plpgsql: for-in-array |