postgresql 检查 Postgres (plpgsql) 中是否存在序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11905868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if sequence exists in Postgres (plpgsql)
提问by Ilia Choly
I'm trying to test, within a stored procedure, whether a sequence already exists.
我正在尝试在存储过程中测试序列是否已经存在。
IF EXISTS SEQUENCE seq_name
RAISE EXCEPTION 'sequence % already exists!', seq_name
END IF;
I have tried several variations of the snippet above without luck. I must be giving Google the wrong terms because I can't seem to find anything on the topic. Any help is appreciated!
我已经尝试了上面代码片段的几种变体，但没有运气。我一定给 Google 提供了错误的术语，因为我似乎找不到有关该主题的任何内容。任何帮助表示赞赏！
采纳答案by rfusca
You should be able query the pg_class table to see if the relname exists.
您应该能够查询 pg_class 表以查看 relname 是否存在。
IF EXISTS (SELECT 0 FROM pg_class where relname = '<my sequence name here>' )
THEN
--stuff here
END IF;
回答by kgrittn
The answer from @rfusca works if you're sure that the name could only be valid for a sequence (i.e., you're confident that it would not be use for an ordinary table, index, view, composite type, TOAST table, or foreign table), and you're not concerned about multiple schemas. In other words, it works for most common cases, but it's not entirely rigorous.
如果您确定名称只能对序列有效（即，您确信它不会用于普通表、索引、视图、复合类型、TOAST 表或外部表），并且您不关心多个模式。换句话说，它适用于大多数常见情况，但并不完全严格。
If you want to test whether a sequenceby that name exists in a particular schema, this should work:
如果要测试特定模式中是否存在该名称的序列，这应该有效：
-- 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;
回答by Erwin Brandstetter
Update: Simply testing for existence has become simpler with to_regclass()in Postgres 9.4:
更新：to_regclass()在 Postgres 9.4 中，简单地测试是否存在变得更加简单：
SELECT to_regclass('schema_name.table_name');
But read the details:
但请阅读详细信息：
Complete function
功能齐全
You need to check for anytable-like object that would conflict with the name, not just sequences.
您需要检查与名称冲突的任何类似表的对象，而不仅仅是序列。
This function creates a new sequence if the name is available and issues a meaningful NOTICE/ WARNING/ EXCEPTIONrespectively in other cases:
这个函数创建一个新的序列名称是否可用，发出有意义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.
_seq .. sequence name
_schema .. schema name (optional; default is CURRENT_SCHEMA)';
Call:
称呼：
SELECT f_create_seq('myseq', 'myschema');
Or:
或者：
SELECT f_create_seq('myseq1'); -- defaults to current schema
Explain
解释
Also read the comment to the function at the end of the code.
Works in Postgres 9.1+. For older versions, you only need to replace
format()- which defends against SQL injection. Details:Two separate parameters allow sequences in any schema independent of the current
search_pathand also allowquote_ident()to do its job.quote_ident()fails with schema-qualified names - would be ambiguous.There is a default value for the schema parameter, so you can omit it from the call. If no schema is given, the function defaults to the
current_schema. Per documentation:current_schemareturns the name of the schema that is first in the search path (or a null value if the search path is empty). This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.List of types for
pgclass.relkindin the manual.
另请阅读代码末尾的函数注释。
适用于 Postgres 9.1+。对于旧版本，您只需要替换
format()- 这可以防止 SQL 注入。细节：两个单独的参数允许在任何模式中独立于当前模式的序列，
search_path并允许quote_ident()完成其工作。quote_ident()使用模式限定名称失败 - 会不明确。schema 参数有一个默认值，因此您可以在调用中省略它。如果没有给出模式，函数默认为
current_schema. 根据文档：current_schema返回搜索路径中第一个模式的名称（如果搜索路径为空，则返回空值）。这是将用于在未指定目标架构的情况下创建的任何表或其他命名对象的架构。pgclass.relkind手册中的类型列表。
回答by Dave Kok
How about using the information schema :
如何使用信息模式：
SELECT COUNT(*)
FROM information_schema.sequences
WHERE sequence_schema=? AND sequence_name=?
回答by k_o_
I'm not sure about the actual intent why the presence of the sequence must be checked. An alternative if the goal is to check if a sequence exists before creating it, the IF NOT EXISTScondition in PostgreSQL can be used:
我不确定为什么必须检查序列的存在的实际意图。如果目标是在创建序列之前检查序列是否存在，IF NOT EXISTS则可以使用 PostgreSQL 中的条件：
CREATE SEQUENCE IF NOT EXISTS 'name'
See https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
见https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
回答by xingang
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';
Result:
结果：
relname | relnamespace
-------------------------------------
updater_state_id_seq | 32898
This query can check existence of a sequence inside a schema.
此查询可以检查模式中序列的存在。
