From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Hostbaek <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect return in where clause
Date: Tue, 28 Jan 2003 10:56:28 -0500
Message-ID: <13472.1043769388@sss.pgh.pa.us> (raw)
In-Reply-To: <20030128112123.GC67742@mich2.itxmarket.com>
References: <20030128112123.GC67742@mich2.itxmarket.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mich@the-lab.org
Subject: Re: subselect return in where clause
In-Reply-To: <13472.1043769388@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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