From: Bart Degryse <Bart.Degryse@indicator.be>
To: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: Select in From clause
Date: Mon, 12 Nov 2007 09:23:18 +0100
Message-ID: <47381B85.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
References: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
Consider this:
CREATE TABLE "public"."test" (
"id" INTEGER NOT NULL,
"tbl" TEXT
) WITHOUT OIDS;
INSERT INTO "public"."test" ("id", "tbl") VALUES (1, 'status');
INSERT INTO "public"."test" ("id", "tbl") VALUES (2, 'yearplan');
Following two statements will return one record.
select tbl from test where id = 1
select * from (select tbl from test where id = 1) a
tbl
status
Following statement will return all records from table 'test' where the 'tbl' field contains a 'y'.
select * from (select tbl from test) a where a.tbl like '%y%'
tbl
yearplan
So it does work. Just change you statement to something like:
SELECT * FROM (SELECT name, condition FROM bar WHERE conditions) AS b WHERE b.condition = xxx;
or
SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.name = xxx;
>>> "Ray Madigan" <ray@madigans.org> 2007-11-09 18:21 >>>
I have never seen this done before, but it seems like it is supposed to work
from reading the manual.
I want to be able to get a table name from another table and use it in the
from clause of a select.
Something like
SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.condition
= xxx;
which translates to something like
SELECT * FROM Dealer AS b WHERE b.zipcode = 12345;
The translated version works but the SELECT in FROM version reports that
b.condition does not exist.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
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: Bart.Degryse@indicator.be
Subject: Re: Select in From clause
In-Reply-To: <47381B85.A3DD.0030.0@indicator.be>
* 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