Re: Recursive queries? - Mailing list pgsql-hackers

Clinging to sanity, ronz@ravensfield.com (Andrew Rawnsley) mumbled into her beard:
> I haven't had any problems with it so far, although I haven't really
> stressed it yet.  I was going to make this very plea...
>
> I agree that the syntax can probably be improved, but its familiar to
> those of us unfortunate enough to have used (or still have to use)
> Oracle. I imagine that bringing it more in line with any standard
> would be what people would prefer.

The SQL:1999 form is instead of the form
with recquery (a,b,c,d) as    (select a1,b1,c1,d1 from some table where d1 > 21)  select * from recquery;

Notice that I have indented this in the same way a Lisp programmer
would indent a LET form...
(let   ((a value-for-a)    (b value-for-b)    (c compute-c)    (d 42)) ;;; The ultimate answer...
(compute-something-with-valuesa b c d))
 

In ML, there is an analagous "let/in" construct:

#let a = 1 and    b = 2 and    c = 3 in    a + b * c;;
- : int = 7

That example is oversimplified, a bit, as it does not do anything
recursive.  In order to express a recursive relationship, the query
likely needs to have a UNION ALL, and look more like the following:
with recquery (a,b,c,d) as  (select a,b,c,d from base_table root   -- Root level entries     where c > 200   union all
 select child.a,child.b,child.c,child.d      from recquery parent, base_table child  -- Self-reference here     where
parent.a= child.b  -- The link between nodes...       and c > 200) select a,b,c,d from recquery;
 

The fact that the form of this resembles that of the Lisp/ML "let"
forms means that WITH can be useful in structuring queries as well.
For instance, supposing you're computing a value that gets used
several times, putting it into a WITH clause might allow evading the
need to compute it more than once.

with notrec (radius, pi, month) as  (select radius, 3.1412, date_trunc('month', txn_date) from pie_table)select month,
sum(pi* radius * radius as area), count(*)  from not_rec  where month between '2003-01-01' and '2004-01-01'  group by
month;

has some 'elegance' by virtue of only using date_trunc once over
 select date_trunc('month', txn_date), sum(3.1412 * radius*radius) as   area, count(*) from pie_table where
date_trunc('month',txn_date) between '2003-01-01' and '2004-01-01' group by month;
 

Admittedly, date_trunc() may not be an ideal example, as the date
constraint would work as well with an untruncated date the point is
that in the no-WITH approach, there is an extra use of date_trunc().
But the recomputation that takes place when a functional value is used
both in the result clause and in the WHERE clause is something that
WITH can eliminate.
-- 
"aa454","@","freenet.carleton.ca"
http://www.ntlug.org/~cbbrowne/emacs.html
Lisp Users:
Due to the holiday next Monday, there will be no garbage collection.