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

On Fri, Nov 19, 2010 at 04:11:56PM -0500, Caleb(dot)Welton(at)emc(dot)com wrote:
> The other aspect of the standard that the Postgres implementation
> does not currently support is the fact that unnest is supposed to be
> defined in terms of laterally derived subqueries, e.g. you should be
> able to unnest another element from a from list entry laterally on
> the left.
>
> CREATE TABLE t1(id int, values int[]); SELECT id, a FROM t1
> UNNEST(values) as u(a);
>
> If you consider it in terms of LATERAL, which Postgres also doesn't
> support, then you may find that it works out much more cleanly to
> consider the multi-array unnest in terms of that rather than in
> terms of an outer join. Specifically since arrays are implicitly
> ordered on their ordinality a simple array lookup is much
> easier/more efficient than performing a full fledged join operator.
>
> E.g. the rewrite is: SELECT id, values[i] as a FROM t1
> LATERAL(SELECT generate_series(array_lower(values, 1),
> array_upper(values, 1) ) as lat(i);
>
> But then LATERAL support is something that has been discussed on and
> off for a while without seeing much progress.

Is LATERAL something you'd like to put preliminary support in for? :)

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