On Fri, Nov 19, 2010 at 04:11:56PM -0500, caleb.wel...@emc.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 <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
