wake-up-neo.com

So prüfen Sie, ob eine Tabelle in einem bestimmten Schema vorhanden ist

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.

sqldatabasepostgresqlinformation-schemasearch-path
127
Andrus

Es kommt darauf an, was Sie testen möchten genau.

Informationsschema?

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.

Systemkataloge

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 auch pg_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'
   );

Alternative: Umwandlung in 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

235
Erwin Brandstetter

Vielleicht benutze information_schema :

SELECT EXISTS(
    SELECT * 
    FROM information_schema.tables 
    WHERE 
      table_schema = 'company3' AND 
      table_name = 'tableincompany3schema'
);
41
kong

Für PostgreSQL 9.3 oder weniger ... Oder wer mag alles auf Text normalisiert

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;
0
Peter Krauss