sqlpostgresqlinheritancedatabase-designpostgresql-8.4

Select (retrieve) all records from multiple schemas using Postgres

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.

Solution

  • 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.

    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 is a handy way to tell the source of each row. But it interacts with the search_path. See:

    fiddle
    Old sqlfiddle