On Sat, 2006-08-26 at 22:46 -0400, Jonah H. Harris wrote: > On 8/26/06, Alvaro Herrera <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq