查找基于表值的模式 [英] Find out which schema based on table values

问题描述

我的数据库分为基于客户端的模式（即：每个客户端都有自己的模式，具有相同的数据结构）。

不知道应该定位哪个模式的操作。它来自系统的另一部分，没有客户端的概念，并且不知道它在哪个客户端集合中操作。在处理它之前，我必须找出请求需要定位的模式。

为了找到正确的模式，我必须找出哪个模式保存了记录 R 具有特定的唯一ID（字符串）

根据我的理解，以下

  SET search_path TO schema1，schema2，schema3，... 
  

只会浏览schema1中的表（或与表匹配的第一个模式），并且不会进行全局搜索。

对于我来做一个全局搜索所有的模式，或者我只需要使用一个for循环并遍历它们，一次一个？

解决方案

您可以使用 继承 。 （请务必考虑 限制 。）

考虑这个小演示：

  CREATE SCHEMA master; - 无法访问其他人。
 
 CREATE SEQUENCE master.myseq; - 全局序列具有全局唯一的id 
 CREATE table master.tbl（
 id int primary key DEFAULT nextval（'master.myseq'）
，foo text）; 
 
 CREATE SCHEMA x; 
 CREATE table x.tbl（）INHERITS（master.tbl）; 
 INSERT INTO x.tbl（foo）VALUES（'x'）; 
 
 CREATE SCHEMA y; 
 CREATE table y.tbl（）INHERITS（master.tbl）; 
 INSERT INTO y.tbl（foo）VALUES（'y'）; 
 
 
 SELECT * FROM x.tbl; - 返回'x'
 SELECT * FROM y.tbl; - 返回'y'
 SELECT * FROM master.tbl; - 返回'x'和'y'< - ！ 
 
  - 清理一切：
  -  DROP SCHEMA x，y，master CASCADE; 
  

现在，要实际标识特定行所在的表，请使用 tableoid ：

  SELECT *，tableoid :: regclass AS table_name 
 FROM master.tbl 
 WHERE id = 2; 
  

结果：

 code> id | foo | table_name 
 --- + ----- + ----------- 
 2 | y | y.tbl 
  

您可以从 tableoid ，最好通过直接使用 tableoid 查询系统目录。 （显示的名称取决于 search_path 的设置。）

  SELECT n.nspname 
 FROM master.tbl t 
 JOIN pg_class c ON c.oid = t.tableoid 
 JOIN pg_namespace n ON c.relnamespace = n.oid 
 WHERE t.id = 2; 
  

这也比循环多个单独的表格要快很多 p>

My database is separated into schemas based on clients (i.e.: each client has their own schema, with same data structure).

I also happen to have an external action that does not know which schema it should target. It comes from another part of the system that has no concepts of clients and does not know in which client's set it is operating. Before I process it, I have to find out which schema that request needs to target

To find the right schema, I have to find out which holds the record R with a particular unique ID (string)

From my understanding, the following

SET search_path TO schema1,schema2,schema3,...

will only look through the tables in schema1 (or the first schema that matches the table) and will not do a global search.

Is there a way for me to do a global search across all schemas, or am I just going to have to use a for loop and iterate through all of them, one at a time?

解决方案

You could use inheritance for this. (Be sure to consider the limitations.)

Consider this little demo:

CREATE SCHEMA master;  -- no access of others ..

CREATE SEQUENCE master.myseq;  -- global sequence to have globally unique id
CREATE table master.tbl (
  id int primary key DEFAULT nextval('master.myseq')
, foo text);

CREATE SCHEMA x;
CREATE table x.tbl() INHERITS (master.tbl);
INSERT INTO  x.tbl(foo) VALUES ('x');

CREATE SCHEMA y;
CREATE table y.tbl() INHERITS (master.tbl);
INSERT INTO  y.tbl(foo) VALUES ('y');


SELECT * FROM x.tbl;  -- returns 'x'
SELECT * FROM y.tbl;  -- returns 'y'
SELECT * FROM master.tbl;  -- returns 'x' and 'y' <-- !!

-- clean it all up:
-- DROP SCHEMA x, y, master CASCADE;

Now, to actually identify the table a particular row lives in, use the tableoid:

SELECT *, tableoid::regclass AS table_name
FROM   master.tbl
WHERE  id = 2;

Result:

id | foo | table_name
---+-----+-----------
2  | y   | y.tbl

You can derive the source schema from the tableoid, best by querying the system catalogs with the tableoid directly. (The displayed name depends on the setting of search_path.)

SELECT n.nspname 
FROM   master.tbl   t
JOIN   pg_class     c ON c.oid = t.tableoid
JOIN   pg_namespace n ON c.relnamespace = n.oid
WHERE  t.id = 2;

This is also much faster than looping through many separate tables.

这篇关于查找基于表值的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！