Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f925KDn19118 for ; Tue, 2 Oct 2001 01:20:13 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9257fI07000; Tue, 2 Oct 2001 01:07:41 -0400 (EDT) To: Markus Wagner cc: pgsql-sql@postgresql.org Subject: Re: using SQL to evaluate arbitrary expressions? In-reply-to: <01100206321504.01063@magnus> References: <01100206321504.01063@magnus> Comments: In-reply-to Markus Wagner message dated "Tue, 02 Oct 2001 06:32:15 +0200" Date: Tue, 02 Oct 2001 01:07:41 -0400 Message-ID: <6997.1001999261@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/6 X-Sequence-Number: 4961 Markus Wagner writes: > We are glad to have found out that we can evaluate such expressions > simply by calling them with SELECT, e. g. "SELECT 29 - 2 > 18" returns true. > We think that it would be very nice to benefit from the pg database system > parser. But can we rely on this functionality in the future? Is this > standard SQL? Well, it's not standard SQL: SQL92 requires a FROM clause in SELECT. But I can assure you it won't disappear from Postgres; it's too darn useful. The customary workaround, as seen in systems like Oracle, is to have a dummy table containing exactly one row; Oracle calls the dummy table DUAL. (Anyone know why? Seems like SINGLE would be more sensical...) Then you can do SELECT 29 - 2 > 18 FROM dual; The fact that your expression doesn't actually *use* any values from the one table row doesn't bother the system; you get one evaluation of the expression, you're happy, and so is the SQL standard. My advice is to just write "SELECT 29 - 2 > 18", because it's clearer in intent, simpler, and some tiny fraction faster than specifying a useless table reference. But you can take comfort in the fact that there's a simple workaround if you're ever forced to use a DBMS that won't accept this syntax. regards, tom lane