To: "Richard Sydney-Smith" <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use derived expression in select statement 
In-reply-to: <000f01c37a78$4ea53560$076c32d2@athlon2000> 
References: <000f01c37a78$4ea53560$076c32d2@athlon2000>
Comments: In-reply-to "Richard Sydney-Smith" <richard@ibisaustralia.com>
	message dated "Sun, 14 Sep 2003 12:25:58 +0800"
Date: Sun, 14 Sep 2003 00:38:42 -0400
Message-ID: <7110.1063514322@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Richard Sydney-Smith" <richard@ibisaustralia.com> writes:
> select dy_c , dy_sect as tsect, (cy_bfwd + dy_p1T4) as curr_bal from fclitot
> where dy_yr = 0 and (curr_bal) <-0.005

> In postgres it tells me curr_bal is not found.

As it should --- this is completely illegal according to the SQL
standard.  It's not even well-defined.  The SQL evaluation model
is that WHERE clause processing is done *before* evaluation of
the select list.

The usual way to avoid writing common subexpressions is to use a
sub-select, for example

select dy_c , dy_sect as tsect, curr_bal
from (select *, (cy_bfwd + dy_p1T4) as curr_bal from fclitot) as ss
where dy_yr = 0 and (curr_bal) <-0.005

This doesn't necessarily save you from evaluating the curr_bal
expression twice, mind you.  It just saves you from writing it out
twice.

			regards, tom lane