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 <[email protected]> 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.
pgpyoS2XMqcbx.pgp
Description: PGP signature
