Re: Hierarchical Queries--Status

On Sat, 2006-08-26 at 22:46 -0400, Jonah H. Harris wrote:
> On 8/26/06, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > Actually I was thinking in the design rather than the code ...
>
> Doh! We hadn't posted the design just yet. Let me write him and see
> where he's at and we'll throw something together for the list.

[Note to Jonah: I've tried sending a similar version of this email to
you a couple of times, but I'm not sure that it's getting through, hence
the post to -hackers in the hope you may be able to pick it up there.]

Hi everyone,

I've had a chance to sit down for a day or so to see how to approach
adding hierarchical queries to PostgreSQL, so I thought I'd post my
initial thoughts on how to proceed. My aim is to refine the list below
based upon feedback from hackers until it gets to the point where I can
start work on it. Here's what I've got so far:

1) Add detection of the WITH clause to the parser.

2) Create a new type of RangeTblEntry to represent each common table
expression specified within the WITH clause where the subquery field
points to the nodes representing the common table expression.

3) Add planner support so that WITH clauses are mapped to a new type of
node that utilises two tuplestores - an output tuplestore and a working
tuplestore. The output tuple store will in effect be the contents of the
table expression while the working tuplestore holds the results of the
last iteration if recursive. Also implement some kind of WithState node
which keeps track of the recursion state.

Having spent some more time today looking at 1) and also at the SQL 2003
spec, it would seem that other databases offer the WITH clause within
subqueries and also as part of a view. I'd be interested to hear
feedback from other developers as to whether it would be possible to
achieve this level of support within PostgreSQL.

Many thanks,

Mark.