Thread: using SQL to evaluate arbitrary expressions?

Hi,

we need to process user-defined integrity conditions, which are stored as 
strings at application level, e. g. "person.age - 2 > 18". We would like to 
substitute the variable names within these expressions by their values at 
trigger time. So we have expressions containing constants only, e. g. "29 - 2 
> 18". 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?

Thank you,
Markus

Markus Wagner <wagner@imsd.uni-mainz.de> 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