Select rows from table where each row refers to a different schema

Question

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?

Answer 1

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:

CREATE TABLE master.portal(portal_name text);

All other tables in various schemata inherit from it, while possibly adding more local columns:

CREATE TABLE a.portal(portal_id serial PRIMARY KEY, col2 text)
INHERITS (master.portal);

CREATE TABLE b.portal(portal_id serial PRIMARY KEY, col2 text, col3 text)
INHERITS (master.portal);

A SELECT query targeted on the master table would then search all child tables as well:

SELECT portal_name, tableoid::regclass::text AS source
FROM   master.portal
WHERE  portal_name ILIKE '%test%';

More details in this closely related answer on SO:

• Retrieve all records from multiple schemas in a PostgreSQL database