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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026