sintaxis - procedimientos almacenados en postgresql pdf

Compruebe si existe la secuencia en Postgres(plpgsql)

Question

Estoy tratando de probar, dentro de un procedimiento almacenado, si ya existe una secuencia.

IF EXISTS SEQUENCE seq_name
    RAISE EXCEPTION ''sequence % already exists!'', seq_name
END IF;

He intentado varias variaciones del fragmento de arriba sin suerte. Debo darle a Google los términos incorrectos porque parece que no puedo encontrar nada sobre el tema. Cualquier ayuda es apreciada!

Answer 1

¿Qué hay de usar el esquema de información?

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

Answer 2

Actualización: Simplemente probar la existencia se ha vuelto más simple con to_regclass() en Postgres 9.4 :

SELECT to_regclass(''schema_name.table_name'');

Pero lea los detalles:

• Cómo verificar si una tabla existe en un esquema dado

Función completa

Debe buscar cualquier objeto similar a una tabla que pudiera entrar en conflicto con el nombre, no solo con las secuencias.

Esta función crea una nueva secuencia si el nombre está disponible y emite un NOTICE / WARNING / EXCEPTION significativo respectivamente en otros casos:

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.
$1 _seq    .. sequence name 
$2 _schema .. schema name (optional; default is CURRENT_SCHEMA)'';

Llamada:

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

O:

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

Explique

• También lea el comentario a la función al final del código.

• Trabaja en Postgres 9.1+ . Para versiones anteriores, solo necesita reemplazar el format() , que se defiende contra la inyección de SQL. Detalles:

  • Inyección de SQL en funciones de Postgres vs consultas preparadas

• Dos parámetros separados permiten secuencias en cualquier esquema independiente de la search_path actual y también permiten a quote_ident() hacer su trabajo. quote_ident() falla con nombres calificados de esquema - sería ambiguo.

• Hay un valor predeterminado para el parámetro de esquema, por lo que puede omitirlo de la llamada. Si no se proporciona ningún esquema, la función se establece de forma predeterminada en el esquema current_schema . Por documentación:

  current_schema devuelve el nombre del esquema que está primero en la ruta de búsqueda (o un valor nulo si la ruta de búsqueda está vacía). Este es el esquema que se utilizará para las tablas u otros objetos nombrados que se crean sin especificar un esquema de destino.

• Lista de tipos para pgclass.relkind en el manual .

• Códigos de error de PostgreSQL .

Answer 3

Debería poder consultar la tabla pg_class para ver si existe el nombre de rel.

IF EXISTS (SELECT 0 FROM pg_class where relname = ''<my sequence name here>'' )
THEN
  --stuff here
END IF;

Answer 4

La respuesta de @rfusca funciona si está seguro de que el nombre solo podría ser válido para una secuencia (es decir, está seguro de que no se usaría para una tabla, índice, vista, tipo compuesto, tabla TOSTADA o tabla ordinaria). tabla externa), y no le preocupan los múltiples esquemas. En otras palabras, funciona para la mayoría de los casos comunes, pero no es del todo riguroso.

Si desea probar si existe una secuencia con ese nombre en un esquema particular, esto debería funcionar:

-- Clear the search path so that the regclass of the sequence
-- will be schema-qualified.
SET search_path = '''';
-- Do your conditional code.
IF EXISTS (SELECT * FROM pg_class
             WHERE relkind = ''S''
               AND oid::regclass::text = ''public.'' || quote_ident(seq_name))
  THEN
    RAISE EXCEPTION ''sequence public.% already exists!'', seq_name
END IF;
-- Restore the normal search path.
RESET search_path;

Answer 5

No estoy seguro de la intención real por la que se debe verificar la presencia de la secuencia. Una alternativa si el objetivo es verificar si existe una secuencia antes de crearla, se puede usar la condición IF NOT EXISTS en PostgreSQL:

CREATE SEQUENCE IF NOT EXISTS ''name''

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

Answer 6

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

Resultado:

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

Esta consulta puede verificar la existencia de una secuencia dentro de un esquema.