Re: Reimplementing UNION/INTERSECT/EXCEPT

> Plan-tree structure (output of planner): UNION/UNION ALL are handled
> same as now, except that what we are appending together is not directly
> the top-level plan of each leaf query, but a SubqueryScan plan scanning
> the output of each leaf query.  This gives us one extra level of
> projection (targetlist evaluation) in which to put conversions to the
> common union datatype --- without breaking the semantics of GROUP BY,
> DISTINCT, and so forth in the leaf queries.  INTERSECT and EXCEPT will
> be handled by building SubqueryScan plans that emit the common-datatype
> columns plus a resjunk boolean column that shows whether the tuple is
> coming from the left or right input.  The outputs of these plans are
> then appended together, sorted, and fed to a new plan node type that
> implements INTERSECT(ALL) and EXCEPT(ALL).  It will be a simple
> generalization of the Unique plan type: scan a set of successive tuples
> that agree in all the non-resjunk columns, counting the number of tuples
> that came from left and right sides.  This gives us 'm' and 'n' for each
> set, from which the spec-defined behavior can be implemented immediately.

Sounds good.  We have always done these by applying our existing tools. 
Now that we have a better tool (subselects in FROM), we have a better
solution.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026