Postgresql 9.1 select from all schemas

Question

I have a Postgresql 9.1 database with couple hundred schemas. All have same structure, just different data. I need to perform a select on a table and get data from each schema. Unfortunately I haven't found a decent way to do it.

I tried setting the search path to schema_1,schema_2, etc and then perform a select on the table but it only selects data from the first schema.

The only way I managed to do it so far is by generating a big query like:

select * from schema_1.table
union
select * from schema_2.table
union
(...another 100 lines....)

Is there any other way to do this in a more reasonable fashion? If this is not possible, can I at least find out which of the schemas has records in that table without performing this select?

Answer 1

Different schemas mean different tables, so if you have to stick to this structure, it'll mean unions, one way or the other. That can be pretty expensive. If you're after partitioning through the convenience of search paths, it might make sense to reverse your schema:

Store a big table in the public schema, and then provision views in each of the individual schemas.

Check out this sqlfiddle that demonstrates my concept:

http://sqlfiddle.com/#!12/a326d/1

Also pasted inline for posterity, in case sqlfiddle is inaccessible:

Schema:

CREATE SCHEMA customer_1;
CREATE SCHEMA customer_2;

CREATE TABLE accounts(id serial, name text, value numeric, customer_id int);
CREATE INDEX ON accounts (customer_id);

CREATE VIEW customer_1.accounts AS SELECT id, name, value FROM public.accounts WHERE customer_id = 1;
CREATE VIEW customer_2.accounts AS SELECT id, name, value FROM public.accounts WHERE customer_id = 2;

INSERT INTO accounts(name, value, customer_id) VALUES('foo', 100, 1);
INSERT INTO accounts(name, value, customer_id) VALUES('bar', 100, 1);
INSERT INTO accounts(name, value, customer_id) VALUES('biz', 150, 2);
INSERT INTO accounts(name, value, customer_id) VALUES('baz', 75, 2);

Queries:

SELECT SUM(value) FROM public.accounts;

SET search_path TO 'customer_1';
SELECT * FROM accounts;

SET search_path TO 'customer_2';
SELECT * FROM accounts;

Results:

425

---

1   foo     100
2   bar     100

---

3   biz     150
4   baz     75