X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 19B0ED1CAD8 for ; Mon, 3 Nov 2003 01:46:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53184-09 for ; Sun, 2 Nov 2003 21:46:27 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 91A8ED1CAE4 for ; Sun, 2 Nov 2003 21:46:24 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hA31kONu006049 for ; Mon, 3 Nov 2003 01:46:24 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hA31iJsE005647 for pgsql-sql@postgresql.org; Mon, 3 Nov 2003 01:44:19 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.sql Subject: Re: A tricky sql-query... Date: Sun, 02 Nov 2003 20:40:15 -0500 Organization: cbbrowne Computing Inc Lines: 34 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:gMBJzF5Xrx2CIOap9f0Ln0x3TS0= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/9 X-Sequence-Number: 15824 Quoth mark@summersault.com (Mark Stosberg): > On 2003-10-22, Timo wrote: >> >> You can't have any recursion in an pure sql-query, can you? > > It depends on how you think of recursion, I'd say. You join on the same > table a number of times, by giving it a different alias each time. You > have to manually specify (or generate with application code) all these > aliases and joins, though. Sometimes people use this technique to > implement tree structures in SQL. There is apparently an SQL 1999 feature that expressly supports recursion. WITH RECURSIVE Q1 AS SELECT ... FROM ... WHERE ... Q2 AS SELECT ... FROM ... WHERE ... SELECT ... FROM Q1, Q2 WHERE ... See example here... There is presumably some work ongoing; support for WITH RECURSIVE is on the TODO list, and has been discussed before... http://archives.postgresql.org/pgsql-hackers/2003-05/msg00657.php -- output = reverse("gro.mca" "@" "enworbbc") http://www.ntlug.org/~cbbrowne/linuxdistributions.html "Listen, strange women, lyin' in ponds, distributin' swords, is no basis for a system of government. Supreme executive power derives itself from a mandate from the masses, not from some farcical aquatic ceremony." -- Monty Python and the Holy Grail