To: Michael Hostbaek <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect return in where clause 
In-reply-to: <20030128112123.GC67742@mich2.itxmarket.com> 
References: <20030128112123.GC67742@mich2.itxmarket.com>
Comments: In-reply-to Michael Hostbaek <mich@the-lab.org>
	message dated "Tue, 28 Jan 2003 12:21:23 +0100"
Date: Tue, 28 Jan 2003 10:56:28 -0500
Message-ID: <13472.1043769388@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Hostbaek <mich@the-lab.org> writes:
> select some_stuff, more_stuff, 
> (select other_stuff from other_table where other_table.stuff ilike
> stuff.match) as other_stuff from stuff where other_stuff = 'hello'

The above is nonsensical: WHERE cannot refer to values computed in
the SELECT list, because WHERE logically occurs before the SELECT
list is executed (indeed, the SELECT list will not be executed at
all, if WHERE returns false).

You could repeat yourself:

select some_stuff, more_stuff, 
(select other_stuff from other_table where other_table.stuff ilike
 stuff.match) as other_stuff from stuff
where
(select other_stuff from other_table where other_table.stuff ilike
 stuff.match) = 'hello'

or if it really bugs you to write the expression twice, perhaps
use a sub-select:

select * from
(select some_stuff, more_stuff, 
 (select other_stuff from other_table where other_table.stuff ilike
  stuff.match) as other_stuff from stuff) as subselect
where other_stuff = 'hello'

although you shouldn't have any illusions about this being more
efficient than writing the expression twice.

			regards, tom lane