0

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?

  • 1
    In order to get a meaningful answer (longer than 'Look into dynamic SQL'), please give an example of how portal tables differ among customers. – dezso Feb 23 '15 at 9:18
  • 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. – user3526905 Feb 24 '15 at 4:05
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.