From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Caleb(dot)Welton(at)emc(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org, itagaki(dot)takahiro(at)gmail(dot)com |
Subject: | Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) |
Date: | 2010-11-20 01:00:54 |
Message-ID: | 20101120010054.GA24567@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-11-20 03:18:22 | Re: [PATCH] Custom code int(32|64) => text conversions out of performance reasons |
Previous Message | Steve Singer | 2010-11-20 01:00:46 | Re: Patch to add a primary key using an existing index |