Проверьте, существует ли последовательность в Postgres (plpgsql)

Я пытаюсь проверить в рамках хранимой процедуры, существует ли последовательность уже.

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

Я попробовал несколько вариантов фрагмента выше без удачи. Я должен дать Google неправильные термины, потому что я не могу найти что-то по этой теме. Любая помощь приветствуется!

Источник

6 ответов

Решение

Вы должны быть в состоянии запросить таблицу pg_class, чтобы увидеть, существует ли relname.

IF EXISTS (SELECT 0 FROM pg_class where relname = '<my sequence name here>' )
THEN
  --stuff here
END IF;
Источник

Ответ от @rfusca сработает, если вы уверены, что имя может быть действительным только для последовательности (т. Е. Уверены, что оно не будет использоваться для обычной таблицы, индекса, представления, составного типа, таблицы TOAST или чужая таблица), и вас не волнует несколько схем. Другими словами, это работает для большинства распространенных случаев, но это не совсем строго.

Если вы хотите проверить, существует ли последовательность с таким именем в конкретной схеме, это должно работать:

-- 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;
Источник

Обновление: просто проверка на существование стала проще с to_regclass() в Postgres 9.4:

SELECT to_regclass('schema_name.table_name');

Но прочитайте детали:

Полная функция

Вам нужно проверить любой табличный объект, который конфликтует с именем, а не только последовательности.

Эта функция создает новую последовательность, если имя доступно, и выдает значимый NOTICE / WARNING / EXCEPTION соответственно в других случаях:

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

Вызов:

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

Или же:

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

объяснять

  • Также прочитайте комментарий к функции в конце кода.

  • Работает в Postgres 9.1+. Для более старых версий вам нужно только заменить format() который защищает от SQL-инъекций. Подробности:

  • Два отдельных параметра допускают последовательности в любой схеме независимо от текущей search_path а также разрешить quote_ident() делать свою работу. quote_ident() терпит неудачу с именами, уточненными схемой - было бы неоднозначно.

  • Для параметра схемы есть значение по умолчанию, поэтому вы можете опустить его в вызове. Если схема не указана, по умолчанию используется current_schema, По документации:

    current_schema возвращает имя схемы, которая является первой в пути поиска (или нулевое значение, если путь поиска пуст). Это схема, которая будет использоваться для любых таблиц или других именованных объектов, которые создаются без указания целевой схемы.

  • Список типов для pgclass.relkind в руководстве.

  • Коды ошибок PostgreSQL.

Источник

Как насчет использования информационной схемы:

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

Результат:

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

Этот запрос может проверить существование последовательности внутри схемы.

Источник

Я не уверен насчет реального намерения, почему необходимо проверить наличие последовательности. Альтернативой, если целью является проверка существования последовательности перед ее созданием, IF NOT EXISTS Условие в PostgreSQL можно использовать:

CREATE SEQUENCE IF NOT EXISTS 'name'

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

Источник
Другие вопросы по тегам