Skip site navigation (1) Skip section navigation (2)

Re: SQL subquery to supply table name?

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Scott Chapman <scott_list(at)mischko(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: SQL subquery to supply table name?
Date: 2002-09-27 01:01:44
Message-ID: 20020927010144.GA17362@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
I think the basic response is "don't do that". SQL doesn't support queries
where the tables are not predetermined. The query planner would have a
terrible time since it would not be able to estimate costs prior to
execution.

Since all your "subtables" are likely to have the same structure, why not
store them all in one table. Any particular reason?

On Thu, Sep 26, 2002 at 05:45:20PM -0700, Scott Chapman wrote:
> Here's the details:
>                                   Table "reviews"
>              Column              |  Type   |               Modifiers
> ---------------------------------+---------+----------------------------------------
>  review_num                      | integer | default 
> nextval('review_number'::text)
>  table_quest                     | text    |
> 
> 
>               Table "mpe_quest"
>          Column         |  Type   | Modifiers
> ------------------------+---------+-----------
>  review_num             | integer |
>  txtcompanyname         | text    |
> 
> 
> select foo.txtcompanyname from (select table_quest from reviews where 
> review_num=28) as foo where review_num=28;
> It comes back and says No such attribute or function foo.txtcompanyname.
> 
> 
> Scott
> 
> 
> 
> On Thursday 26 September 2002 04:04 pm, Scott Chapman wrote:
> > I have a situation where a table contains table names.  I need to know if I
> > can make a Postgresql query that will have a subquery which supplies the
> > table name I want to deal with.  Can this be done?
> >
> > Something like this:
> > select column from (select table_name from table2 where key=value) where
> > key=value;
> >
> > Scott
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

In response to

Responses

pgsql-general by date

Next:From: Stephan SzaboDate: 2002-09-27 01:02:10
Subject: Re: SQL subquery to supply table name?
Previous:From: Scott ChapmanDate: 2002-09-27 00:45:20
Subject: Re: SQL subquery to supply table name?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group