Re: UPDATE using sub selects

Hi,

On 3/31/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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.

Yes, I have tried this already. As you suspect, it seems that the subplan
does not get evaluated if its not part of the targetList at all.

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.

Ok, I will try this out.

Regards,
Nikhils

--
EnterpriseDB http://www.enterprisedb.com