SQL subquery to supply table name?

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 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/

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

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

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/

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

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).