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?

• András Váczi: In order to get a meaningful answer (longer than 'Look into dynamic SQL'), please give an example of how portal tables differ among customers.
• user3526905: actually description of each portal inside every customer schema (for example a.portal or b.portal,where a and b are customers )is same.they don't differ in structure.thanks in advance.

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