Узнайте, какая схема основана на табличных значениях

Моя база данных разделена на схемы на основе клиентов (т. Е. У каждого клиента есть собственная схема с одинаковой структурой данных).

У меня также есть внешнее действие, которое не знает, на какую схему оно должно ориентироваться. Оно исходит из другой части системы, которая не имеет понятия о клиентах и ​​не знает, в каком наборе клиентов она работает. Прежде чем я обработаю его, я должен выяснить, на какую схему должен ориентироваться этот запрос.

Чтобы найти правильную схему, мне нужно выяснить, какая из них содержит запись R с определенным уникальным идентификатором (строкой).

Насколько я понимаю, следующее

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

будет просматривать только таблицы в схеме 1 (или первой схеме, которая соответствует таблице) и не будет выполнять глобальный поиск.

Есть ли способ выполнить глобальный поиск по всем схемам, или мне просто придется использовать цикл for и перебирать их все по одной?


django postgresql sql database-connection postgresql-9.2
person Goro    schedule 15.10.2012    source источник
comment
Насколько я понимаю, вы хотите выполнить поиск строки, которую вы ввели, через несколько моделей? Например, скажем, пользователь вводит «привет», вы хотите, чтобы это приветствие искали, скажем, в моделях: автомобили, автобусы, велосипеды. Я правильно понимаю ваш вопрос?   -  person Jonathan    schedule 15.10.2012
comment
@Jonathanq: Нет, каждая схема идентична, и я хочу искать данные в одной и той же модели, только в разных схемах: schema1.cars, schema2.cars, schema3.cars и т. д.   -  person Goro    schedule 15.10.2012

Ответы (3)


arrow_upward
5
arrow_downward

Для этого можно использовать наследование. . (Обязательно учитывайте ограничения< /сильный>.)

Рассмотрим эту небольшую демонстрацию:

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;

Теперь, чтобы фактически определить таблицу, в которой находится конкретная строка, используйте tableoid:

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

Результат:

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;

Кроме того, это намного быстрее, чем перебор множества отдельных таблиц.

person Erwin Brandstetter    schedule 15.10.2012
comment
Звучит сложно. Насколько хорошо django обрабатывает наследование PostgreSQL? - person Goro; 15.10.2012
comment
Я не эксперт по Django, но это совсем не должно быть сложно. На самом деле, если у вас есть одна и та же таблица во многих схемах, это значительно упростит сценарий CREATE. Для ясности: все команды CREATE должны выполняться только один раз. Но ведь это же ясно, верно? Что забыл добавить 8и добавил сейчас): как определить исходную таблицу. - person Erwin Brandstetter; 16.10.2012

arrow_upward
1
arrow_downward

Вам придется перебрать все пространства имен. Вы можете получить много этой информации из pg_* системных каталогов. Теоретически вы должны иметь возможность разрешать сопоставление клиент -> схема во время запроса, не обращаясь к базе данных, чтобы первый вызов SQL, который вы делаете, был:

SET search_path = client1,global_schema;
person Sean    schedule 15.10.2012

arrow_upward
1
arrow_downward

Хотя я думаю, что решение Эрвина, вероятно, предпочтительнее, если вы можете реструктурировать свои таблицы, альтернативой, которая не требует каких-либо изменений схемы, является написание функции PL/PgSQL, которая сканирует таблицы с использованием динамического SQL на основе информации из системного каталога.

Дано:

CREATE SCHEMA a;
CREATE SCHEMA b;

CREATE TABLE a.testtab ( searchval text );
CREATE TABLE b.testtab (LIKE a.testtab);

INSERT INTO a.testtab(searchval) VALUES ('ham');
INSERT INTO b.testtab(searchval) VALUES ('eggs');

Следующая функция PL/PgSQL ищет во всех схемах, содержащих таблицы с именем _tabname, значения в _colname, равные _value, и возвращает первую подходящую схему.

CREATE OR REPLACE FUNCTION find_schema_for_value(_tabname text, _colname text, _value text) RETURNS text AS $$
DECLARE
    cur_schema text;
    foundval integer;
BEGIN
    FOR cur_schema IN 
      SELECT nspname 
      FROM pg_class c 
      INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) 
      WHERE c.relname = _tabname AND c.relkind = 'r'
    LOOP
      EXECUTE 
        format('SELECT 1 FROM %I.%I WHERE %I = $1', 
            cur_schema, _tabname, _colname
        ) INTO foundval USING _value;
      IF foundval = 1 THEN
        RETURN cur_schema;
      END IF;
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

Если совпадений нет, возвращается null. Если есть несколько совпадений, результатом будет одно из них, но нет никаких гарантий относительно того, какое именно. Добавьте предложение ORDER BY в запрос схемы, если вы хотите вернуть (скажем) первое в алфавитном порядке или что-то в этом роде. Функция также тривиально изменена, чтобы возвращать setof text и RETURN NEXT cur_schema, если вы хотите вернуть все совпадения.

regress=# SELECT find_schema_for_value('testtab','searchval','ham');
 find_schema_for_value 
-----------------------
 a
(1 row)

regress=# SELECT find_schema_for_value('testtab','searchval','eggs');
 find_schema_for_value 
-----------------------
 b
(1 row)

regress=# SELECT find_schema_for_value('testtab','searchval','bones');
 find_schema_for_value 
-----------------------

(1 row)

Кстати, вы можете повторно использовать определения таблиц без наследования, если хотите, и вы действительно должны. Либо используйте общий составной тип данных:

CREATE TYPE public.testtab AS ( searchval text );
CREATE TABLE a.testtab OF public.testtab;
CREATE TABLE b.testtab OF public.testtab;

в этом случае они используют один и тот же тип данных, но не какие-либо данные; или или через LIKE:

CREATE TABLE public.testtab ( searchval text );
CREATE TABLE a.testtab (LIKE public.testtab);
CREATE TABLE b.testtab (LIKE public.testtab);

в этом случае они полностью не связаны друг с другом после создания.

person Craig Ringer    schedule 16.10.2012