使用 Postgres 从多个模式中选择(检索)所有记录 [英] Select (retrieve) all records from multiple schemas using Postgres

问题描述

我有一个带有一些架构的 PostgreSQL 数据库，如下所示:

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

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

所有模式都有一个名为 product 的表，其中有一列名为 title.我想知道是否可以执行 select 语句以在特定条件下从所有模式中检索所有记录.

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.

推荐答案

With 继承 就像@Denis 提到的，这会很简单.也适用于 Postgres 8.4.请务必考虑限制.

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

等

表不必共享相同的名称或架构.
然后，您可以一举查询所有表:

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?
这是告诉每一行的来源的一种方便的方法.详情:

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

• 根据表值找出哪个架构

SQL 小提琴.

这篇关于使用 Postgres 从多个模式中选择(检索)所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！