Обсуждение: SQL subquery to supply table name?
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
On Thu, Sep 26, 2002 at 04:04:14PM -0700, 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; The answer is no. You may be able to fudge it by creating a plpgsql function that builds the query on the fly but it will probably suck performance-wise. Sounds like your database structure is not properly normalised. -- Martijn van Oosterhout <kleptog@svana.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.
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
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@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.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.
On Thu, 26 Sep 2002, 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.
Yep, foo has a table_quest column. In 7.3 (now in beta) you probably
could make a function that returns a rowset from the table given as
its argument.
Stephan Szabo wrote:
>
> Yep, foo has a table_quest column. In 7.3 (now in beta) you probably
> could make a function that returns a rowset from the table given as
> its argument.
It will work, but you need to use an anonymous return type (i.e. record) and
specify the columns you are actually returning:
# create table foo(f1 int, f2 text);
CREATE TABLE
# insert into foo values (1,'a');
INSERT 1223680 1
# insert into foo values (2,'b');
INSERT 1223681 1
# CREATE OR REPLACE FUNCTION select_from(text) RETURNS SETOF record AS '
# DECLARE
# sql text;
# rec record;
# BEGIN
# sql := ''SELECT * FROM '' || $1;
# FOR rec IN EXECUTE sql LOOP
# RETURN NEXT rec;
# END LOOP;
# RETURN;
# END;
# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
# select * from select_from('foo') as t(col1 int, col2 text);
col1 | col2
------+------
1 | a
2 | b
(2 rows)
Joe
On Thursday 26 September 2002 06:01 pm, Martijn van Oosterhout wrote: > 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. It's too bad for the query planner then! :-) (I'm not a Postgresql guru and I really don't know what the problem would be if the query planner could not estimate costs beforehand. Don't take my "It's too bad" comment seriously!) > Since all your "subtables" are likely to have the same structure, why not > store them all in one table. Any particular reason? They don't have the same structure. Similar enough for my purposes but not the same. In all but two cases where I handle the data from the sub-tables, I handle columns common to the different tables (so these tables are not normalized completely but I knew that going in) or I hadle all the data as a unit. The two places where I handle the data specifically I determine which type of review it is and direct the user to a different web page to deal with the specifics. I can solve this problem by running two queries rather one query with a subquery but that's not as elegant. I was hoping to find a more elegant solution based on a sub-query. Scott
I was lead to believe that Postgresql would handle this from reading the docs.
This error message from psql made me think it would also:
select txtcompanyname from (select table_quest from reviews where
review_num=28) where review_num=28;
ERROR: sub-SELECT in FROM must have an alias
For example, FROM (SELECT ...) [AS] foo
What is the "sub-SELECT in FROM" supposed to be used for if not this usage?
Scott
On Thursday 26 September 2002 06:01 pm, Martijn van Oosterhout wrote:
> 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?
Scott Chapman wrote:
> I was lead to believe that Postgresql would handle this from reading the docs.
> This error message from psql made me think it would also:
>
> select txtcompanyname from (select table_quest from reviews where
> review_num=28) where review_num=28;
>
> ERROR: sub-SELECT in FROM must have an alias
> For example, FROM (SELECT ...) [AS] foo
>
> What is the "sub-SELECT in FROM" supposed to be used for if not this usage?
>
Read the error message again -- you just need an alias for the sub-select, e.g.:
select
t.table_quest
from
(select table_quest from reviews where review_num=28) AS t;
^^^^^
^^^^^
HTH,
Joe
On Thu, 26 Sep 2002, Scott Chapman wrote: > I was lead to believe that Postgresql would handle this from reading the docs. > This error message from psql made me think it would also: > > select txtcompanyname from (select table_quest from reviews where > review_num=28) where review_num=28; > > ERROR: sub-SELECT in FROM must have an alias > For example, FROM (SELECT ...) [AS] foo > > What is the "sub-SELECT in FROM" supposed to be used for if not this usage? It's effectively as if you defined a view with that as the definition and used it. It mostly exists to allow multiple levels of processing (for example, you want to do a query that does something to a set of grouped data).
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
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