On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote:
> On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.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
onecolumn 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@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