Re: UPDATE using sub selects

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: NikhilS <nikkhils(at)gmail(dot)com>
Cc: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: UPDATE using sub selects
Date: 2007-03-30 20:46:12
Message-ID: 23354.1175287572@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

NikhilS <nikkhils(at)gmail(dot)com> writes:
> I have invented a ROWEXPR_SUBLINK type that handles multiple output columns.
> The trouble is that since eventually columns of the parents have to be part
> of the query's targetList, I am sending the entire subquery as one of the
> entries in that list and the targetList gets populated with entries
> dependent on the subquery much later via make_subplan.

> This breaks code in rewriteTargetList (which expects every list entry to be
> of type TargetEntry), and expand_targetlist (which expects the targets to be
> present in attrno order, the entries added because of the subquery will not
> be in order as compared to normal "SET colname = expr" targets).

Hmm. That sounds like it would be a horrid mess. You need to decouple
the execution of the subplan from the use of its outputs, apparently.
There is some precedent for this in the way that InitPlans are handled:
the result of the subplan is stored into a ParamList array entry that's
later referenced by a Param node in the parent's expression tree. That
would generalize easily enough to setting more than one Param, but I'm
not clear on where you'd want to stick the subplan itself in the plan
tree, nor on what controls how often it needs to get evaluated.

An alternative approach is to put the subplan into the rangetable and
use Vars to reference its outputs. Again it's not quite clear what
drives re-execution of the subplan. It strikes me though that an
approach like this might also serve for SQL2003's LATERAL construct,
which'd be a nice thing to support.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2007-03-30 20:48:21 Re: Oracle indemnifies PostgreSQL on its patents
Previous Message Tom Lane 2007-03-30 20:35:50 Re: [PATCHES] Full page writes improvement, code update