Re: SQL99 Hierarchical queries - Mailing list pgsql-hackers

Hello hackers,
> Thanks very much for doing this work.  Is there some way you can
> summarize what you did so others can join you in working on it?  If it
> is easier for you to write this in some language other than English,
> please do, and we'll find translators :)
> 
> I noticed that the patch touches the parser, the optimizer and the
> executor.  What does it to with each?  What did I miss?
> 
This is some info about implementations.

Parser.    WITH aliased queries stored as list of SUBQUERY nodes. Each of
them is parsed as usually. In pstate->withClause already analyzed part
of WITH queries list is stored. When next WITH subquery is analyzed,
it's been added to subqueries list inside withClause node, so any WITH
subquery can see al prevoius WITH subqueries.   In FROM clause all WITH aliases represented by special type of
RangTblEntry - RTE_WITH_SUBQUERY. It stores a reference to WithClause
node and index of itself in QITH subqueries list.   For analyzing var added step to search in WITH aliases if they are
present.  Recursiveness support. Before WITH subquery analyzing in
 
pstate->withClause->calias  stored it's alias. So when transforming
FROM clause item and relation name found only in
pstate->withClause->calias query marked as recursive. SQL99 recursive
queries are made using UNION and first UNION subquery should be non
recursive (this isn't currently checked). Thus when transforming set
operation statement, after analyzing of first statement it's is RTE
stored in pstate->withClause->cRTE, and all checks for vars in this
WITH subquery in made against this RTE.

Optimizer.   WithClause node transformed to With node, scan of RTEs of type
RTE_WITH_SUBQUERY to WithScan nodes. Each WITH subquery is enveloped
into WithSubPlan node. It stores result and working tuplestores, and
some flags (used for prevent double initialization and execution) for
each subquery.
Nothing extraordinary is done here.

Executor.   When executor tries to fetch first tuple from any WithScan node,
this node check whether With node have been executed, if no then it
executes it and then fetches all it's tuples from it's result
tuplestore.   "With" node being executed is simply call ExecNode on each subplan
in it's list and storing tuples in result tuplestore.   Recursiveness support. It's all done in Append node. If it
marked
as recusrive, it changes a little it's behaviour.   Tuples fetched from subplan are stored in workin table. When
Append reaches the end of list of its subqueries it call
nodeWithSwitchTables. This function for query being executed will
append result table to final table, move working table to result
table, and clean working table. After this Append begins next loop of
subqueries execution, starting from 2nd subquery. Thus first Append
subquery is executed only once. Execution ends when no one tuple
fetched from all subqueries.   This approach allows WithScan nodes to fetch data fetched by
Append in previous loop.

Regards, Evgen.