Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id AC62937C671
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 12 Nov 2007 04:23:35 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 69099-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 12 Nov 2007 04:23:32 -0400 (AST)
Received: from interscan.indicator.com (unknown [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id 8748837C5A1
	for <pgsql-sql@postgresql.org>; Mon, 12 Nov 2007 04:23:31 -0400 (AST)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Mon, 12 Nov 2007 09:21:20 +0100
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Mon, 12 Nov 2007 09:23:28 +0100
Message-Id: <47381B85.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Mon, 12 Nov 2007 09:23:18 +0100
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: "Pgsql-Sql" <pgsql-sql@postgresql.org>
Subject: Re: Select in From clause
References: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
In-Reply-To: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__Part7C5A0E66.0__="
X-imss-version: 2.049
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/44
X-Sequence-Number: 29572

--=__Part7C5A0E66.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Consider this:
CREATE TABLE "public"."test" (
  "id" INTEGER NOT NULL,=20
  "tbl" TEXT
) WITHOUT OIDS;

INSERT INTO "public"."test" ("id", "tbl") VALUES (1, 'status');
INSERT INTO "public"."test" ("id", "tbl") VALUES (2, 'yearplan');
=20
Following two statements will return one record.
select tbl from test where id =3D 1
select * from (select tbl from test where id =3D 1) a=20
=20

tbl
status
=20
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
=20
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 =3D xxx;
or
SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.name =
=3D 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.conditio=
n
=3D xxx;

which translates to something like

SELECT * FROM Dealer AS b WHERE b.zipcode =3D 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

--=__Part7C5A0E66.0__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-15=
">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Consider this:</DIV>
<DIV>CREATE TABLE "public"."test" (<BR>&nbsp; "id" INTEGER NOT NULL, =
<BR>&nbsp; "tbl" TEXT<BR>) WITHOUT OIDS;</DIV>
<DIV><BR>INSERT INTO "public"."test" ("id", "tbl") VALUES (1, 'status');</D=
IV>
<DIV>INSERT INTO "public"."test" ("id", "tbl") VALUES (2, 'yearplan');</DIV=
>
<DIV>&nbsp;</DIV>
<DIV>Following two statements will return one record.</DIV>
<DIV>select tbl from test where id =3D 1</DIV>
<DIV>
<DIV>select * from (select tbl from test where id =3D 1) a </DIV>
<DIV>&nbsp;</DIV></DIV>
<DIV>
<TABLE cellSpacing=3D1 cellPadding=3D4 border=3D1>
<TBODY>
<TR>
<TD class=3DThRows>tbl</TD></TR>
<TR>
<TD class=3DTrOdd align=3Dleft>status</TD></TR></TBODY></TABLE></DIV>
<DIV>&nbsp;</DIV>
<DIV>Following statement will return all records from table 'test' where =
the 'tbl' field contains a 'y'.</DIV>
<DIV>select * from (select tbl from test) a where a.tbl like '%y%'<BR></DIV=
>
<DIV>
<TABLE cellSpacing=3D1 cellPadding=3D4 border=3D1>
<TBODY>
<TR>
<TD class=3DThRows>tbl</TD></TR>
<TR>
<TD class=3DTrOdd align=3Dleft>yearplan</TD></TR></TBODY></TABLE></DIV>
<DIV>&nbsp;</DIV>
<DIV>So it does work. Just change you statement to something like:</DIV>
<DIV>SELECT * FROM (SELECT name, condition&nbsp;FROM bar WHERE conditions) =
AS b WHERE b.condition =3D xxx;</DIV>
<DIV>or</DIV>
<DIV>SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE =
b.name =3D xxx;<BR><BR><BR>&gt;&gt;&gt; "Ray Madigan" &lt;ray@madigans.org&=
gt; 2007-11-09 18:21 &gt;&gt;&gt;<BR>I have never seen this done before, =
but it seems like it is supposed to work<BR>from reading the manual.<BR><BR=
>I want to be able to get a table name from another table and use it in =
the<BR>from clause of a select.<BR><BR>Something like<BR><BR>SELECT * FROM =
(SELECT name FROM bar WHERE conditions) AS b WHERE b.condition<BR>=3D =
xxx;<BR><BR>which translates to something like<BR><BR>SELECT * FROM Dealer =
AS b WHERE b.zipcode =3D 12345;<BR><BR>The translated version works but =
the SELECT in FROM version reports that<BR>b.condition does not exist.<BR><=
BR><BR>---------------------------(end of broadcast)-----------------------=
----<BR>TIP 9: In versions below 8.0, the planner will ignore your desire =
to<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; choose an index scan if your =
joining column's datatypes do not<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
match<BR></DIV></BODY></HTML>

--=__Part7C5A0E66.0__=--