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

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