I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals
, plugin
, … When I want to select portals used by customer 'a', I can do select * from a.portal
.
I want to select all the customers having a particular 'portal name'. But portal table is inside customer schema. So how should I query in this situation?
I have found solution using a PHP script but is there anything that we can do on the DB level?
Best Answer
You could use inheritance for this. You would have a "master" schema with template tables and only the basic set of columns that all inheriting tables share:
All other tables in various schemata inherit from it, while possibly adding more local columns:
A
SELECT
query targeted on the master table would then search all child tables as well:More details in this closely related answer on SO: