Comment vérifier si une table existe dans un schéma donné

La firebase database Postgres 8.4 et versions ultérieures contient des tables communes dans public schémas public et des tables spécifiques à l’ company dans le schéma de l’ company .
company noms de schéma d’ company commencent toujours par 'company' et se terminent par le numéro d’entreprise.
Donc, il peut y avoir des schémas:

 public company1 company2 company3 ... companynn 

Une application fonctionne toujours avec une seule entreprise.
Le search_path est spécifié en conséquence dans la chaîne de connexion odbc ou npgsql, comme:

 search_path='company3,public' 

Comment vérifier si une table donnée existe dans un schéma de société spécifié?

 select isSpecific('company3','tablenotincompany3schema') 

devrait retourner false , et

 select isSpecific('company3','tableincompany3schema') 

devrait retourner true .

Dans tous les cas, la fonction ne doit vérifier que les schémas de l’ companyn , pas les autres schémas.

Si une table donnée existe à la fois dans le schéma public et le schéma passé, la fonction doit retourner true .
Cela devrait fonctionner pour Postgres 8.4 ou version ultérieure.

Cela dépend de ce que vous voulez tester exactement .

Schéma d’information?

Pour trouver “si la table existe” ( peu importe qui le demande ), l’interrogation du schéma d’ information_schema.tables ( information_schema.tables ) est incorrecte , à proprement parler, car ( par documentation ):

Seules les tables et les vues auxquelles l’utilisateur actuel a access (en tant que propriétaire ou disposant de certains privilèges) sont affichées.

La requête démontrée par @kong peut renvoyer FALSE , mais la table peut toujours exister. Il répond à la question:

Comment vérifier si une table (ou une vue) existe et que l’utilisateur actuel y a access?

 SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name = 'table_name' ); 

Le schéma d’information est principalement utile pour restr portable sur les principales versions et sur les différents SGBDR. Mais l’implémentation est lente, car Postgres doit utiliser des vues sophistiquées pour se conformer à la norme ( information_schema.tables est un exemple assez simple). Et certaines informations (comme les OID) sont perdues lors de la traduction à partir des catalogues système – qui contiennent toutes les informations.

Catalogues système

Votre question était:

Comment vérifier si une table existe?

 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 ); 

Utilisez les catalogues système pg_class et pg_namespace directement, ce qui est également beaucoup plus rapide. Cependant, par documentation sur pg_class :

Le catalogue pg_class catalogue les tables et la plupart des autres colonnes pg_class colonnes ou similaires à une table. Cela inclut les index (mais voir aussi pg_index ), les séquences , les vues , les vues matérialisées , les types composites et les tables TOAST ;

Pour cette question particulière, vous pouvez également utiliser la vue système pg_tables . Un peu plus simple et plus portable sur les principales versions de Postgres (ce qui n’est guère préoccupant pour cette requête de base):

 SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = 'schema_name' AND tablename = 'table_name' ); 

Les identifiants doivent être uniques parmi tous les objects mentionnés ci-dessus. Si vous voulez demander:

Comment vérifier si un nom pour une table ou un object similaire dans un schéma donné est pris?

 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' ); 
  • Réponse connexe sur dba.SE discutant des “schémas d’information et des catalogues système”

Alternative: cast à regclass

 SELECT 'schema_name.table_name'::regclass 

Cela déclenche une exception si la table (éventuellement qualifiée par le schéma) (ou un autre object occupant ce nom) n’existe pas.

Si vous ne qualifiez pas le nom de la table de manière regclass , une regclass à regclass est regclass défaut sur search_path et renvoie l’OID pour la première table trouvée – ou une exception si la table ne figure dans aucun des schémas répertoriés. Notez que les schémas système pg_catalog et pg_temp (le schéma des objects temporaires de la session en cours) font automatiquement partie de search_path .

Vous pouvez utiliser cela et intercepter une exception possible dans une fonction. Exemple:

  • Vérifiez si la séquence existe dans Postgres (plpgsql)

Une requête comme ci-dessus évite les exceptions possibles et est donc légèrement plus rapide.

to_regclass(rel_name) dans Postgres 9.4+

Beaucoup plus simple maintenant:

 SELECT to_regclass('schema_name.table_name'); 

Identique à la dissortingbution, mais ça revient …

… null plutôt que de lancer une erreur si le nom n’est pas trouvé

Peut-être utiliser information_schema :

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

Pour PostgreSQL 9.3 ou moins … Ou qui aime tout normalisé au texte

Trois versions de mon ancienne bibliothèque SwissKnife: relname_exists(anyThing) , relname_normalized(anyThing) et relnamechecked_to_array(anyThing) . Tous les contrôles à partir de la table pg_catalog.pg_class , et renvoie les types de données universels standard ( booléen , texte ou texte []).

 /** * 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_ssortingng(relname_to_array($1,$2), '.'), CASE WHEN $3 THEN '' ELSE NULL END) $wrap$ language SQL IMMUTABLE;