Re: Recursive queries

In an attempt to throw the authorities off his trail, kleptog(at)svana(dot)org (Martijn van Oosterhout) transmitted:
> On Mon, Jan 24, 2005 at 05:27:46PM +0100, tmp wrote:
>> Are there any plans on implementing support for recursive queries in
>> postgresql in the near future? If so: When?
>>
>> I can see there has been some discussion on the subject in the
>> developer-group for quite some time ago, but aparently all thoughts of
>> recursive queries has been stalled. :-(
>
> What do you mean by resursive queries? A query can have a subquery
> which calls a function which executes another query. That counts as
> recursion in my book. What type of recursion are you thinking of?

By recursive queries, we mean the form defined in SQL3/SQL.1999.

IBM DB2 uses a syntax like the following; I'd have to rummage around
for extra books to verify standards conformance, but hopefully this
gives the idea...

WITH tmp_rel (object, subobject, quantity) AS
(SELECT part, child_part, quantity FROM
partlist root
WHERE root.part in ('ASSEMBLY 1', 'ASSEMBLY 2', 'ASSEMBLY 3')
UNION ALL
SELECT child.part, child.child_part, child.quantity
FROM partlist child, tmp_rel parent
WHERE parent.subobject = child.part)
SELECT DISTINCT object, subobject, quantity
FROM tmp_rel;

What you add in is the "WITH" clause that lets you define a (possibly
self-referencing) query which you then reference below.

This is more or less equivalent to the "let" clause offered in
languages like Lisp and ML

let disc = (x*x-y*y) in
calculate_with_squares (disc)
calculate_again_with_squares (disc);;

Or

(let ((disc (+ (* x x) (* y y))))
(calculate_with_squares disc)
(calculate_again_with_squares disc))

In Lisp, the thing that allows recursing is, strictly speaking, called
"letrec"...

Nonetheless, the similarity is still quite evident. SQL "WITH" allows
building self-referencing queries, as well as allowing you to better
organize bits that are likely to get reused. If I have some complex
subquery that occurs several times in a query, I might want to use
WITH in a not-so-recursive way to factor out that subquery so it only
needs to be expressed once.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/linux.html
"Life. Don't talk to me about life."
-- Marvin the Paranoid Android