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

问题描述

我有一个具有一些模式的Postgres 8.4数据库，如下所示：

  My_Database 
 |  - >模式
 |  - > AccountA 
 |  - > AccountB 
 |  - > AccountC 
 |  - > AccountD 
 |  - > AccountE 
 ... 
 |  - > AccountZ 
  

所有模式都有一个名为的产品它有一个名为 title 的列。我想知道是否可以执行select语句来检索所有具有特定条件的模式的记录。

到目前为止，我发现的唯一方法是通过帐户运行查询帐户，如下所示。

  SET search_path TO AccountA; 
 
 SELECT title FROM product WHERE title ILIKE'％test％'; 
  

模式动态创建，所以我不知道他们的名字或有多少人存在。

解决方案

使用 继承 ，如 @Denis提到的，这将非常简单。也为Postgres 8.4工作。请务必考虑 限制 。

基本上，你会有一个master表，我想在一个主模式中：

  CREATE TABLE master.product（title text）; 
  

各种模式中所有其他表从其继承，可能添加更多本地列：

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

等。

不得不共享相同的名称或模式。

然后您可以单击查询所有表：

<$ AS $
FROM master.product
WHERE标题ILIKE'％test％';

tableoid :: regclass :: text ？

这是一个方便的方法来告诉每一行的来源。详细信息：

• 根据表值查找哪个模式

SQL Fiddle。

I have a Postgres 8.4 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.

解决方案

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.

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?
That's a handy way to tell the source of each row. Details:

• Find out which schema based on table values

SQL Fiddle.

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