Datenbanken ab Postgres 8.4 enthalten allgemeine Tabellen im Schema public
und firmenspezifische Tabellen im Schema company
.company
Schemanamen beginnen immer mit 'company'
und mit der Firmennummer enden.
Es kann also Schemata geben wie:
public
company1
company2
company3
...
companynn
Eine Anwendung funktioniert immer mit einer einzelnen Firma.
Das search_path
wird in der Verbindungszeichenfolge odbc oder npgsql wie folgt angegeben:
search_path='company3,public'
Wie würden Sie prüfen, ob eine bestimmte Tabelle in einem angegebenen companyn
Schema vorhanden ist?
z.B:
select isSpecific('company3','tablenotincompany3schema')
sollte false
und zurückgeben
select isSpecific('company3','tableincompany3schema')
sollte true
zurückgeben.
In jedem Fall sollte die Funktion nur das übergebene companyn
- Schema überprüfen, keine anderen Schemata.
Wenn eine bestimmte Tabelle sowohl in public
als auch im übergebenen Schema vorhanden ist, sollte die Funktion true
zurückgeben.
Es sollte für Postgres 8.4 oder höher funktionieren.
Es kommt darauf an, was Sie testen möchten genau.
Um herauszufinden, ob die Tabelle existiert ( egal wer fragt), ist die Abfrage des Informationsschemas (information_schema.tables
) falsch genau genommen, weil ( laut Dokumentation ):
Es werden nur die Tabellen und Ansichten angezeigt, auf die der aktuelle Benutzer Zugriff hat (als Eigentümer oder mit bestimmten Berechtigungen).
Die Abfrage demonstriert durch @kong kann FALSE
zurückgeben, aber die Tabelle kann noch existieren. Es beantwortet die Frage:
Wie überprüfe ich, ob eine Tabelle (oder Sicht) existiert und der aktuelle Benutzer Zugriff darauf hat?
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name = 'table_name'
);
Das Informationsschema ist hauptsächlich nützlich, um über Hauptversionen und über verschiedene RDBMS hinweg portabel zu bleiben. Die Implementierung ist jedoch langsam, da Postgres anspruchsvolle Ansichten verwenden muss, um dem Standard zu entsprechen (information_schema.tables
Ist ein recht einfaches Beispiel). Und einige Informationen (wie OIDs) gehen bei der Übersetzung aus den Systemkatalogen verloren - die eigentlich alle Informationen enthalten.
Ihre Frage war:
Wie überprüfe ich, ob eine Tabelle existiert?
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
);
Verwenden Sie direkt die Systemkataloge pg_class
Und pg_namespace
, Was ebenfalls erheblich schneller ist. Allerdings laut Dokumentation auf pg_class
:
Der Katalog
pg_class
Katalogisiert Tabellen und fast alles andere, das Spalten enthält oder auf andere Weise einer Tabelle ähnelt. Dies beinhaltet Indizes (siehe auchpg_index
), Sequenzen , Ansichten , materialisierte Ansichten , zusammengesetzte Typen und TOAST-Tabellen ;
Für diese spezielle Frage können Sie auch die Systemansicht pg_tables
verwenden. Ein bisschen einfacher und portabler für alle wichtigen Postgres-Versionen (was für diese grundlegende Abfrage kaum von Belang ist):
SELECT EXISTS (
SELECT 1
FROM pg_tables
WHERE schemaname = 'schema_name'
AND tablename = 'table_name'
);
Die Bezeichner müssen für die oben genannten all Objekte eindeutig sein. Wenn Sie fragen möchten:
Wie prüfe ich, ob ein Name für eine Tabelle oder ein ähnliches Objekt in einem bestimmten Schema verwendet wird?
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'
);
regclass
SELECT 'schema_name.table_name'::regclass
Dies löst eine Ausnahme aus wenn die (optional schemaqualifizierte) Tabelle (oder ein anderes Objekt, das diesen Namen belegt) nicht existiert.
Wenn Sie den Tabellennamen nicht schemaqualifizieren, wird bei der Umwandlung in regclass
standardmäßig search_path
und gibt OID für die erste gefundene Tabelle zurück - oder eine Ausnahme, wenn sich die Tabelle in keinem der aufgelisteten Schemata befindet. Beachten Sie, dass die Systemschemata pg_catalog
und pg_temp
(Das Schema für temporäre Objekte der aktuellen Sitzung) sind automatisch Teil von search_path
.
Sie können das verwenden und eine mögliche Ausnahme in einer Funktion abfangen. Beispiel:
Eine Abfrage wie oben vermeidet mögliche Ausnahmen und ist daher etwas schneller.
to_regclass(rel_name)
in Postgres 9.4+Jetzt viel einfacher:
SELECT to_regclass('schema_name.table_name');
Wie die Besetzung, , aber es wird zurückgegeben ...
... null, anstatt einen Fehler auszulösen, wenn der Name nicht gefunden wird
Vielleicht benutze information_schema :
SELECT EXISTS(
SELECT *
FROM information_schema.tables
WHERE
table_schema = 'company3' AND
table_name = 'tableincompany3schema'
);
Drei Varianten meiner alten SwissKnife-Bibliothek: relname_exists(anyThing)
, relname_normalized(anyThing)
und relnamechecked_to_array(anyThing)
. Alle Überprüfungen aus der Tabelle pg_catalog.pg_class und geben Standard-Universaldatentypen zurück (boolean, text oder text []) .
/**
* 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;