Postgresql 9.1 从所有模式中选择

Question

我有一个带有几百个模式的 Postgresql 9.1 数据库。都具有相同的结构，只是不同的数据。我需要对表执行选择并从每个模式获取数据。不幸的是，我还没有找到合适的方法来做到这一点。

我尝试将搜索路径设置为 schema_1、schema_2 等，然后对表执行选择，但它只从第一个模式中选择数据。

到目前为止，我设法做到这一点的唯一方法是生成一个大查询，例如:

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

有没有其他更合理的方式来做到这一点？如果这不可能，我是否可以在不执行此选择的情况下至少找出哪些模式在该表中有记录？

Answer 1

不同的架构意味着不同的表，因此如果您必须坚持这种结构，则意味着联合，无论是哪种方式。那可能非常昂贵。如果您希望通过方便的搜索路径进行分区，那么反转您的模式可能是有意义的:

在公共(public)架构中存储一个大表，然后在每个单独的架构中提供 View 。

查看这个演示我的概念的 sqlfiddle:

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

还为后代粘贴了内联，以防 sqlfiddle 无法访问:

架构:

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);

查询:

SELECT SUM(value) FROM public.accounts;

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

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

结果:

425

---

1   foo     100
2   bar     100

---

3   biz     150
4   baz     75