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

是否有其他更合理的方法来完成这个任务？如果不可能，请问我能否在不执行此select的情况下找出哪个模式在该表中具有记录？

- Biggie Mac

Answer 1

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

在公共模式中存储一个大表，然后在每个单独的模式中提供视图。

请查看这个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

- Fitzsimmons

---

这不是一个好的方法，因为你需要手动为每个 schema 创建一行。我建议创建一个函数。Skype 使用 Plproxy 通过代理数据库和包装函数在多个数据库 shard 上运行查询。 - user9452835

Answer 2

如果你想了解表格中的数据，就必须使用SELECT语句。没有其他方法。模式只是逻辑寻址 - 对于你的情况很重要，所以你使用了很多表格，并且必须进行大量的UNION操作。

search_path按预期工作。它没有意义 - 返回指定模式的数据，但它指定了搜索未完全限定的表格的顺序。搜索在第一个具有请求名称的表格上结束。

注意：大量的UNION操作可能需要大量的内存。

你可以使用动态SQL和带有临时表的存储过程：

postgres=# DO $$
   declare r record;
   begin
     drop table if exists result;
     create temp table result as select * from x.a limit 0; -- first table;
     for r in select table_schema, table_name
                  from information_schema.tables
                 where table_name = 'a'
     loop
       raise notice '%', r;
       execute format('insert into result select * from %I.%I',
                                          r.table_schema,
                                          r.table_name);
     end loop;
   end; $$;

结果：

注意：(y,a)
注意：(x,a)
执行
postgres=# 从结果中选择*;
 a  
----
  1
  2
  3
  4
  5
 ..

- Pavel Stehule

Answer 3

这里有一种方法。您需要预先提供所有目标模式的架构名称。如果您知道要使用每个模式，则可以像Pavel展示的那样更改为循环遍历所有模式。在我的示例中，我关心包含名为bar的表的三个模式。逻辑将在每个模式的bar表上运行选择并将值插入结果表中。最后，您将拥有来自所有表的所有数据的表。您可以更改此设置以进行更新、删除或执行DDL。我选择保持简单，只是从每个模式中的每个表中收集数据。

--START SETUP AKA Run This Section Once
create table schema3.bar(bar_id   SERIAL PRIMARY KEY,
                         bar_name VARCHAR(50) NOT NULL);

insert into schema1.bar(bar_name) select 'One';
insert into schema2.bar(bar_name) select 'Two';
insert into schema3.bar(bar_name) select 'Three';
--END SETUP

DO $$
   declare r record;
   DECLARE l_id INTEGER = 1;
   DECLARE l_schema_name TEXT;
   begin
     drop table if exists public.result;
     create table public.result (bar_id INTEGER, bar_name TEXT);

     drop table if exists public.schemas;
     create table public.schemas (id serial PRIMARY KEY, schema_name text NOT NULL);
     INSERT INTO public.schemas(schema_name)
     VALUES ('schema1'),('schema2'),('schema3');


     for r in select *
              from public.schemas 
     loop
       raise notice '%', r;

       SELECT schema_name into l_schema_name
       FROM public.schemas
       WHERE id = l_id;

       raise notice '%', l_schema_name;
       EXECUTE 'set search_path TO ' || l_schema_name;

      EXECUTE 'INSERT into public.result(bar_id, bar_name) select bar_id, bar_name from ' || l_schema_name || '.bar';

       l_id = l_id + 1;
     end loop;

   end; $$;


--DEBUG
   select * from schema1.bar;
   select * from schema2.bar;
   select * from schema3.bar;

   select * from public.result;
   select * from public.schemas;

   --CLEANUP
   --DROP TABLE public.result;
   --DROP TABLE public.schemas;

- Kuberchaun