Jak sprawdzić, czy tabela istnieje w danym schemacie
Baza danych Postgres 8.4 i większa zawiera tabele wspólne w schemacie public
oraz tabele specyficzne dla firm w schemacie company
.company
nazwy schematów zawsze zaczynają się od 'company'
i kończą na numerze firmy.
Więc mogą być Schematy:
public
company1
company2
company3
...
companynn
Aplikacja zawsze współpracuje z jedną firmą.search_path
jest odpowiednio określony w łańcuchu połączeń odbc lub npgsql, jak:
search_path='company3,public'
Jak sprawdzić czy dana tabela istnieje w określonym companyn
schemat?
select isSpecific('company3','tablenotincompany3schema')
Powinien zwrócić false
, oraz
select isSpecific('company3','tableincompany3schema')
Powinien zwrócić true
.
W każdym przypadku funkcja powinna sprawdzać tylko companyn
schemat przekazany, a nie inne schematy.
Jeśli dana tabela istnieje zarówno w public
, jak i przekazanym schemacie, funkcja powinna zwrócić true
.
Powinien działać dla Postgres 8.4 lub nowszych.
3 answers
To zależy od tego, co chcesz przetestować dokładnie..
Schemat informacyjny?
Aby znaleźć "czy tabela istnieje" ( bez względu na to, kto pyta ), odpytywanie schematu informacyjnego (information_schema.tables
) jest niepoprawne , ściśle mówiąc, ponieważ (w dokumentacji):
Tylko te tabele i widoki są pokazane, że bieżący użytkownik ma dostęp do (poprzez bycie właścicielem lub posiadanie jakiegoś przywileju).
The zapytanie pokazane przez @ kong może zwrócić FALSE
, ale tabela może nadal istnieć. Odpowiada na pytanie:
Jak sprawdzić, czy tabela (lub widok) istnieje, a bieżący użytkownik ma do niej dostęp?
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name = 'table_name'
);
Schemat informacji jest głównie przydatny, aby pozostać przenośnym w głównych wersjach i w różnych systemach RDBM. Implementacja jest jednak powolna, ponieważ Postgres musi używać wyrafinowanych widoków, aby dostosować się do standardu (information_schema.tables
jest dość prostym przykład). A niektóre informacje (jak OIDs) gubią się w tłumaczeniu z katalogów systemowych - które faktycznie przenoszą wszystkie informacje.
Katalogi systemowe
Twoje pytanie brzmiało:
Jak sprawdzić, czy tabela istnieje?
SELECT EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
);
Użyj katalogów systemowych pg_class
i pg_namespace
bezpośrednio, co jest również znacznie szybsze. Jednakże na podstawie dokumentacji na pg_class
:
Katalog
pg_class
katalogi tabele i najbardziej Wszystko inne co ma kolumny lub jest w inny sposób podobny do tabeli. Obejmuje to indeksy (alepg_index
), sekwencje, widoki, zmaterializowane widoki, kompozytowe typy i stoły tostowe ;
Do tego konkretnego pytania można również użyć widoku systemowego pg_tables
. Nieco prostsze i bardziej przenośne w głównych wersjach Postgres (co nie jest problemem dla tego podstawowego zapytanie):
SELECT EXISTS (
SELECT 1
FROM pg_tables
WHERE schemaname = 'schema_name'
AND tablename = 'table_name'
);
Identyfikatory muszą być unikalne wśród WSZYSTKICH wymienionych powyżej obiektów. Jeśli chcesz zapytać:
Jak sprawdzić, czy nazwa tabeli lub podobnego obiektu w danym schemacie jest brana?
SELECT EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
);
Alternatywa: Obsada do regclass
SELECT 'schema_name.table_name'::regclass
To podnosi exception jeśli tabela (lub inny obiekt o tej nazwie) nie istnieje.
Jeśli nie podasz nazwy tabeli, rzut do regclass
jest domyślnie ustawiony na search_path
i zwraca OID dla pierwszej znalezionej tabeli - lub wyjątek, jeśli tabela nie znajduje się w żadnym z wymienionych schematów. Zauważ, że schematy systemowe pg_catalog
i pg_temp
(schemat dla obiektów tymczasowych bieżącej sesji) są automatycznie częścią search_path
.
Możesz tego użyć i złapać ewentualny wyjątek w funkcji. Przykład:
Zapytanie jak powyżej pozwala uniknąć ewentualnych wyjątków i dlatego jest nieco szybsze.
to_regclass(rel_name)
W Postgres 9.4 +
Teraz znacznie prościej:
SELECT to_regclass('schema_name.table_name');
Tak samo jak obsada, ale powraca ...
... null zamiast rzucać error if the name is not found
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:54:41
Być może użyj information_schema :
SELECT EXISTS(
SELECT *
FROM information_schema.tables
WHERE
table_schema = 'company3' AND
table_name = 'tableincompany3schema'
);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-12-14 13:45:23
Dla PostgreSQL 9.3 lub mniej...Lub kto lubi wszystkie znormalizowane do tekstu
Trzy smaki mojej starej biblioteki SwissKnife:relname_exists(anyThing)
, relname_normalized(anyThing)
i relnamechecked_to_array(anyThing)
. Wszystkie kontrole z tabeli pg_catalog.pg_class i zwraca standardowe uniwersalne typy danych (boolean, tekst lub tekst []).
/**
* From my old SwissKnife Lib to your SwissKnife. License CC0.
* Check and normalize to array the free-parameter relation-name.
* Options: (name); (name,schema), ("schema.name"). Ignores schema2 in ("schema.name",schema2).
*/
CREATE FUNCTION relname_to_array(text,text default NULL) RETURNS text[] AS $f$
SELECT array[n.nspname::text, c.relname::text]
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
regexp_split_to_array($1,'\.') t(x) -- not work with quoted names
WHERE CASE
WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1] AND c.relname = x[2]
WHEN $2 IS NULL THEN n.nspname = 'public' AND c.relname = $1
ELSE n.nspname = $2 AND c.relname = $1
END
$f$ language SQL IMMUTABLE;
CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
SELECT EXISTS (SELECT relname_to_array($1,$2))
$wrap$ language SQL IMMUTABLE;
CREATE FUNCTION relname_normalized(text,text default NULL,boolean DEFAULT true) RETURNS text AS $wrap$
SELECT COALESCE(array_to_string(relname_to_array($1,$2), '.'), CASE WHEN $3 THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-18 01:39:07