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

Folks,

I'd like to see about getting an enhanced UNNEST, first the one
according to the SQL standard, namely with an optional WITH ORDINALITY
clause, and possibly some extra enhancements.

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?

Also, there's been some enthusiasm for a different kind of enhancement
of UNNEST, which would go something like this:

UNNEST(anyarray, number_of_dimensions_to_unnest)

That'd probably be a separate patch, though.

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

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

BTW, what will we return for arrays with 2 or more dimensions?
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)

--
Itagaki Takahiro

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