Geek Answers Handbook

Access to PostgreSQL dynamic table

I have a products schema and some tables there. Each table in the products schema has an id , and with this id I can get this table name, for example

 products \ product1 \ product2 \ product3

I need to select information from dynamic access to the corresponding product, for example

 SELECT * FROM 'products.'(SELECT id from categories WHERE id = 7);

Of course this does not work ...
How can I do something like this in PostgreSQL?

+2
source share
3 answers

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 select:

 SELECT * FROM getProductById(7) AS b (category_id int, ... );

works for PostgreSQL 9.x

+3
source

If you can change the layout of the database to use partitioning , this is likely to be the way. Then you can just access the "master" table, as if it were a single table, and not several subnets.

You can create a view that joins tables with an additional column corresponding to the table. If all your queries set a value for this extra column, the scheduler should be smart enough to skip scanning all the other tables.

Or you could write a function in PL / pgSQL using EXECUTE to build the appropriate query after retrieving the table name. A function can even return a set so that it can be used in a FROM clause in the same way as a table reference. Or you can simply execute the same query construct in your application logic.

+2
source

For me, it looks like you have a problem with the main scheme: shouldn't you have only one product table with a category in it?

Perhaps you support the site mentioned in this article?

http://thedailywtf.com/Articles/Confessions-The-Shopping-Cart.aspx

0
source

More articles:


All Articles