sql - Select (retrieve) all records from multiple schemas using Postgres

Question

I have a PostgreSQL database with some schemas, like below:

My_Database
 |-> Schemas
    |-> AccountA
    |-> AccountB
    |-> AccountC
    |-> AccountD
    |-> AccountE
           .
           .
           .
    |-> AccountZ

All schemas have a table called product which has a column called title. I would like to know if is possible to execute a select statement to retrieve all records from all schemas with a certain conditional.

The only way I found until now is to run a query account by account, like below.

SET search_path TO AccountA;

SELECT title FROM product WHERE title ILIKE '%test%';

Schemas are created dynamically, so I don't know their names or how many of them exist.

Answer 1

With inheritance like @Denis mentioned, this would be very simple. Works for Postgres 8.4, too. Be sure to consider the limitations.

Basically, you would have a master table, I suppose in a master schema:

CREATE TABLE master.product (title text);

And all other tables in various schemata inherit from it, possibly adding more local columns:

CREATE TABLE a.product (product_id serial PRIMARY KEY, col2 text)
INHERITS (master.product);

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

etc.

The tables don't have to share the same name or schema.
Then you can query all tables in a single fell swoop:

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

tableoid::regclass::text?
That's a handy way to tell the source of each row. Details:

• Find out which schema based on table values

SQL Fiddle.