I have a products schema and some tables there.
Each table in products schema has an id, and by this id I can get this table name, e.g.
products
\ product1
\ product2
\ product3
I need to select info from dynamic access to appropriate product, e.g.
SELECT * FROM 'products.'(SELECT id from categories WHERE id = 7);
Of course, this doesn't work...
How I can do something like that in PostgreSQL?
OK, I found a solution:
CREATE OR REPLACE FUNCTION getProductById(cid int) RETURNS RECORD AS $$
DECLARE
result RECORD;
BEGIN
EXECUTE 'SELECT * FROM ' || (SELECT ('products.' || (select category_name from category where category_id = cid) || '_view')::regclass) INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
and to select:
SELECT * FROM getProductById(7) AS b (category_id int, ... );
works for PostgreSQL 9.x
If you can change your database layout to use partitioning instead, that would probably be the way to go. Then you can just access the "master" table as if it were one table rather than multiple subtables.
You could create a view that combines the tables with an extra column corresponding to the table it's from. If all your queries specify a value for this extra column, the planner should be smart enough to skip scanning all the rest of the tables.
Or you could write a function in PL/pgSQL, using the EXECUTE command to construct the appropriate query after fetching the table name. The function can even return a set so it can be used in the FROM clause just as you would a table reference. Or you could just do the same query construction in your application logic.
To me, it sounds like you've a major schema design problem: shouldn't you only have one products table with a category_id in it?
Might you be maintaining the website mentioned in this article?
http://thedailywtf.com/Articles/Confessions-The-Shopping-Cart.aspx
