pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect in From clause
3+ messages / 3 participants
[nested] [flat]
* Select in From clause
@ 2007-11-09 17:21 Ray Madigan <ray@madigans.org>
2007-11-09 18:35 ` Re: Select in From clause Alvaro Herrera <alvherre@commandprompt.com>
2007-11-12 08:23 ` Re: Select in From clause Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 3+ messages in thread
From: Ray Madigan @ 2007-11-09 17:21 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select in From clause
2007-11-09 17:21 Select in From clause Ray Madigan <ray@madigans.org>
@ 2007-11-09 18:35 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2007-11-09 18:35 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
Ray Madigan wrote:
> 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;
No, that's not how it works. The stuff returned by the inner select is
a set of rows which can be further operated upon by the outer select.
It is not expanded into a table name.
One way to construct queries is to build plpgsql functions and use
EXECUTE. However, the approach you are using looks like bad practice
(read: bad database design).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select in From clause
2007-11-09 17:21 Select in From clause Ray Madigan <ray@madigans.org>
@ 2007-11-12 08:23 ` Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 3+ messages in thread
From: Bart Degryse @ 2007-11-12 08:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-11-12 08:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-09 17:21 Select in From clause Ray Madigan <ray@madigans.org>
2007-11-09 18:35 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-11-12 08:23 ` Bart Degryse <Bart.Degryse@indicator.be>
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