Date: Fri, 9 Nov 2007 15:35:59 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Ray Madigan <ray@madigans.org>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: Select in From clause
Message-ID: <20071109183559.GA7161@alvh.no-ip.org>
References: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <IIEPJABILGOCODDLDNJFGENDCEAA.ray@madigans.org>
User-Agent: Mutt/1.5.16 (2007-06-11)

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.