pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using SQL to evaluate arbitrary expressions?
2+ messages / 2 participants
[nested] [flat]

* using SQL to evaluate arbitrary expressions?
@ 2001-10-02 04:32 Markus Wagner <wagner@imsd.uni-mainz.de>
  2001-10-02 05:07 ` Re: using SQL to evaluate arbitrary expressions? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Markus Wagner @ 2001-10-02 04:32 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: using SQL to evaluate arbitrary expressions?
  2001-10-02 04:32 using SQL to evaluate arbitrary expressions? Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-10-02 05:07 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-10-02 05:07 UTC (permalink / raw)
  To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-10-02 05:07 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-02 04:32 using SQL to evaluate arbitrary expressions? Markus Wagner <wagner@imsd.uni-mainz.de>
2001-10-02 05:07 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox