Re: Select in From clause

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

 

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%'

 

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