Re: Recursive queries

On Wed, Jan 26, 2005 at 08:52:37AM -0500, Christopher Browne wrote:
> 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.

OMG! While I can understand what you say query does, I simply can't
visualise it at all. Using WITH for named subqueries, that I can
understand, it would even be useful. But self-referencing, I can't even
think of how an executor would even calculate the resultset of the
above query.

There must be some additional constraints, because what happens if I
write a query like:

WITH tmp_rel (num) AS
(SELECT num+1 FROM tmp_rel)
SELECT * FROM tmp_rel;

If I'm understanding you correctly, this query should never complete. I
guess you need to build a query processor smart enough to detect this.
As a base recursive functions should have a seed and an iterator and
the syntax should reflect that. I don't think the WITH syntax is doing
that.

Incidently, for the case people are pointing to recursive queries here,
the contrib/tablefunc module provides an answer with the connectby().
Given the table name, the parent and the child field names it will
return a set resulting from a walk down the tree:

http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/contrib/tablefunc/README.tablefunc?rev=1.11;content-type=text%2Fplain

Anyway, thanks for the info about recurive queries.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.