Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B89761337B6B for ; Thu, 16 Sep 2010 02:13:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 16708-02 for ; Thu, 16 Sep 2010 05:13:07 +0000 (UTC) X-Greylist: delayed 00:09:47.702595 by SQLgrey-1.7.6 Received: from anumail9.anu.edu.au (anumail9.anu.edu.au [130.56.64.135]) by mail.postgresql.org (Postfix) with ESMTP id A16A11337B64 for ; Thu, 16 Sep 2010 02:13:05 -0300 (ADT) Received: from mailout1.anu.edu.au (mailout1.anu.edu.au [130.56.64.232]) by anumail9.anu.edu.au (8.14.4/8.14.4) with ESMTP id o8G53EaM027741 for ; Thu, 16 Sep 2010 15:03:14 +1000 (EST) Received: from mailout1.anu.edu.au (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with SMTP id 493D814A09C2 for ; Thu, 16 Sep 2010 15:03:14 +1000 (EST) Received: from smtphost.anu.edu.au (ds2.anu.edu.au [130.56.64.54]) by mailout1.anu.edu.au (Postfix) with ESMTP id 189BC14A09C3 for ; Thu, 16 Sep 2010 15:03:14 +1000 (EST) Received: from [150.203.7.65] ([150.203.7.65]) by smtphost.anu.edu.au (8.14.4/8.14.4) with ESMTP id o8G53DuO018520 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 16 Sep 2010 15:03:14 +1000 (EST) Message-ID: <4C91A58C.7050406@anu.edu.au> Date: Thu, 16 Sep 2010 15:05:16 +1000 From: Greg Quinn User-Agent: Thunderbird 1.5.0.5 (Macintosh/20060719) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: with queries Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-PMX-Version: 5.5.9.395186, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2010.9.16.45114 internal X-PMX-Outbound: anu.edu.au X-PMX-Info: from-anu X-PerlMx-Spam: Gauge=IIIIIIII, Probability=8%, Report=' BODYTEXTP_SIZE_3000_LESS 0, BODY_SIZE_1000_LESS 0, BODY_SIZE_2000_LESS 0, BODY_SIZE_5000_LESS 0, BODY_SIZE_7000_LESS 0, BODY_SIZE_800_899 0, FROM_EDU_TLD 0, __CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __HAS_MSGID 0, __MIME_TEXT_ONLY 0, __MIME_VERSION 0, __MOZILLA_MSGID 0, __PHISH_SPEAR_STRUCTURE_1 0, __RATWARE_X_MAILER_CS_B 0, __SANE_MSGID 0, __TO_MALFORMED_2 0, __TO_NO_NAME 0, __URI_NO_PATH 0, __URI_NO_WWW 0, __URI_NS , __USER_AGENT 0' X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/46 X-Sequence-Number: 34577 Hullo. I've been a Postgresql user for many years, so forgive me if I'm doing something really stupid. I cannot get 'with' queries to work at all. The test query that I've entered below (as an example) is direct from the section '7.8 WITH Queries' of the manual, so I'd expect that to work wouldn't I? test=# WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100) SELECT sum(n) FROM t; ERROR: syntax error at or near "WITH RECURSIVE" LINE 1: WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM... ^ test=# Tried this with 8.4 and 9.0rc1, same result. Any help (even to tell me that I'm an idiot!) would be much appreciated. In the meantime my query will revert to Plan A, but it would be so much neater using a 'with'. -- Greg Quinn Australian Phenomics Facility greg.quinn@anu.edu.au (02) 61259407