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

mise à jour: simplement tester l'existence est devenu plus simple avec to_regclass() en Postgres 9.4 :

SELECT to_regclass('schema_name.table_name');

mais lisez les détails:

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

Complète de la fonction

, Vous devez vérifier pour tout table-comme l'objet qui serait en conflit avec le nom, pas seulement des séquences.

cette fonction crée une nouvelle séquence si le nom est disponible et émet une NOTICE / WARNING / EXCEPTION dans les autres cas:

CREATE OR REPLACE FUNCTION f_create_seq(_seq text, _schema text = NULL)
  RETURNS void AS
$func$
DECLARE
   _fullname text := format('%I.%I', COALESCE(_schema,current_schema),_seq);
   _relkind "char" := (SELECT c.relkind
                       FROM   pg_namespace n
                       JOIN   pg_class c ON c.relnamespace = n.oid
                       WHERE  n.nspname = COALESCE(_schema, current_schema)
                       AND    c.relname = _seq);
BEGIN
   IF _relkind IS NULL THEN   -- name is free
      EXECUTE 'CREATE SEQUENCE ' || _fullname;
      RAISE NOTICE 'New sequence % created.', _fullname;

   ELSIF _relkind = 'S' THEN  -- 'S' = sequence
      IF has_sequence_privilege(_fullname, 'USAGE') THEN
         RAISE WARNING 'Sequence % already exists.', _fullname;
      ELSE
         RAISE EXCEPTION
           'Sequence % already exists but you have no USAGE privilege.'
         , _fullname;
      END IF;

   ELSE
      RAISE EXCEPTION 'A(n) "%" named % already exists.'
      -- Table-like objects in pg 9.4:
      -- www.postgresql.org/docs/current/static/catalog-pg-class.html
         , CASE _relkind WHEN 'r' THEN 'ordinary table'
                         WHEN 'i' THEN 'index'
                      -- WHEN 'S' THEN 'sequence'  -- impossible here
                         WHEN 'v' THEN 'view'
                         WHEN 'm' THEN 'materialized view'
                         WHEN 'c' THEN 'composite type'
                         WHEN 't' THEN 'TOAST table'
                         WHEN 'f' THEN 'foreign table'
                         ELSE 'unknown object' END
         , _fullname;
   END IF;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_create_seq(text, text) IS
'Create sequence if name is free.
RAISE NOTICE on successful creation.
RAISE WARNING if it already exists.
RAISE EXCEPTION if it already exists and current user lacks USAGE privilege.
RAISE EXCEPTION if object of a different kind occupies the name.
 _seq    .. sequence name 
 _schema .. schema name (optional; default is CURRENT_SCHEMA)';

appel:

SELECT f_create_seq('myseq', 'myschema');

ou:

SELECT f_create_seq('myseq1');  -- defaults to current schema

expliquer

• aussi lire le commentaire à la fonction à la fin du code.

• Travaille dans Postgres 9.1+ . Pour les versions plus anciennes, vous avez seulement besoin de remplacer format() - qui défend contre L'injection SQL. Details:

  • injection SQL dans Postgres fonctions vs les requêtes préparées

• deux des paramètres séparés permettent des séquences dans n'importe quel schéma indépendant de l'actuel search_path et permettent également à quote_ident() de faire son travail. quote_ident() échoue avec des noms schema-qualifiés - serait ambigu.

• il y a une valeur par défaut pour le paramètre schema, donc vous pouvez l'omettre de l'appel. Si aucun schéma n'est donné, la fonction est par défaut current_schema . par documentation:

  current_schema renvoie le nom du schéma qui est le premier dans le chemin de recherche (ou une valeur null si le chemin de recherche est vide). C'est le le schéma qui sera utilisé pour toutes les tables ou autres objets nommés que sont créés sans spécifier de schéma cible.

• liste des types pour pgclass.relkind dans le manuel .

• Codes d'erreur PostgreSQL .

Que Diriez-vous d'utiliser le schéma d'information:

SELECT COUNT(*) 
FROM information_schema.sequences 
WHERE sequence_schema=? AND sequence_name=?

select relname, relnamespace
from pg_class join pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
where n.nspname='metastore_1' and relname='updater_state_id_seq';

résultat:

       relname        | relnamespace 
-------------------------------------
 updater_state_id_seq |        32898

Cette requête peut vérifier l'existence d'une séquence à l'intérieur d'un schéma.

Je ne suis pas sûr de l'intention réelle pourquoi la présence de la séquence doit être vérifiée. Une alternative si le but est de vérifier si une séquence existe avant de la créer, la condition IF NOT EXISTS dans PostgreSQL peut être utilisée:

CREATE SEQUENCE IF NOT EXISTS 'name'

voir https://www.postgresql.org/docs/9.5/static/sql-createsequence.html