在Postgres (plpgsql)中检查序列是否存在

Question

在Postgres (plpgsql)中检查序列是否存在

31

我正在尝试在存储过程中测试一个序列是否已经存在。

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

我尝试了几个以上片段的变体，但都没有成功。可能是因为我给谷歌的搜索词不对，所以我似乎找不到关于这个主题的任何信息。感谢任何帮助！

- Ilia Choly

6个回答

19

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

- kgrittn

2

oid::regclass::text是什么？在我的情况下，relkind是大写的“S”，但对于表格来说，则是小写的'r'。 - Evgeny

在测试之后，我发现quote_ident对于不存在的序列名称无效。 - Evgeny

2

@Evgeny：关于将 regclass 转换为 text，请参考此页面了解有关 regclass 的信息 http://www.postgresql.org/docs/9.2/interactive/datatype-oid.html -- text 只是一个字符字符串。对于 relkind 比较的错误，我很抱歉 -- 确实需要一个大写的 S。我会在答案中进行修正。但是，我不明白您关于 quote_ident 的说法 -- 应该使用它来传递参数；无论该名称是否已存在，都不会影响 quote_ident 函数的行为。 - kgrittn

17

更新：在Postgres 9.4中，使用to_regclass()可更简单地测试存在性：

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注入）。详情请参见：
- Postgres函数与预编译查询中的SQL注入
两个独立的参数允许在任何模式中使用序列，而不受当前search_path的影响，并且还允许quote_ident()执行其工作。quote_ident()无法处理带有模式限定符的名称，因为这将会产生歧义。
模式参数有一个默认值，因此您可以在调用时省略它。如果未指定模式，则该函数默认为current_schema。根据文档说明：

current_schema返回搜索路径中第一个架构的名称（如果搜索路径为空，则返回空值）。这是将用于创建没有指定目标模式的任何表或其他命名对象的模式。
类型列表请参见手册中的pgclass.relkind。
PostgreSQL错误代码。

- Erwin Brandstetter

12

那么使用信息模式如何：

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

- Dave Kok

2

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

此查询可以检查模式中序列的存在性。

- xingang

1

我不确定需要检查序列存在的实际意图。如果目标是在创建序列之前检查序列是否存在，可以使用PostgreSQL中的IF NOT EXISTS条件：

CREATE SEQUENCE IF NOT EXISTS 'name'

请查看 https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

- k_o_

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- rfusca · Accepted Answer

您可以查询 pg_class 表，以查看 relname 是否存在。

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