PostgreSQL序列函数报错：relation不存在，如何按需创建序列？

问题重现 #

执行select teenusjuhtum_generator(2023, 'AA');时触发错误：

SQL Error [42P01]: ERROR: relation "2023_aa" does not exist
错误位置：PL/pgSQL函数teenusjuhtum_generator(integer,text)第17行RETURN处

函数代码如下：

create or replace function teenusjuhtum_generator(year integer, liik text) returns bigint as $$
declare
    _seq_name text := year || '_' || liik;
begin
   case (select c.relkind = 'S':: "char"
         from pg_catalog.pg_namespace n
         join pg_catalog.pg_class c on c.relnamespace = n."oid"
         where n.nspname = current_schema() --or provide your schema
         and c.relname = _seq_name)
   when true then
        raise notice 'true';
   when false then
         RAISE EXCEPTION '% is not a sequence!', _seq_name;
   else
        EXECUTE format('CREATE SEQUENCE %I MINVALUE 1 INCREMENT BY 1', _seq_name);
   end case;
return nextval(_seq_name);
end
$$ language plpgsql

问题原因 #

1. 标识符大小写不匹配：

   • 传入liik = 'AA'时，生成的_seq_name是2023_AA。
   • 使用format('%I', _seq_name)创建序列时，PostgreSQL会为包含大写字母的标识符添加双引号，最终创建的是区分大小写的序列"2023_AA"。
   • 但return nextval(_seq_name);中的_seq_name会被PostgreSQL自动转为小写（未加双引号的标识符默认小写），导致实际查找的是2023_aa，而该序列不存在，触发报错。

2. 序列引用方式不一致：创建序列时用了安全的格式化标识符，但获取序列值时直接使用字符串拼接的名称，没有做格式化处理，导致名称不匹配。

修复方案 #

方案1：统一序列名称为小写 #

create or replace function teenusjuhtum_generator(year integer, liik text) returns bigint as $$
declare
    _seq_name text := year || '_' || lower(liik); -- 统一转为小写
begin
   case (select c.relkind = 'S'::char
         from pg_catalog.pg_namespace n
         join pg_catalog.pg_class c on c.relnamespace = n.oid
         where n.nspname = current_schema()
         and c.relname = _seq_name)
   when true then
        raise notice 'Sequence % exists', _seq_name;
   when false then
         RAISE EXCEPTION '% is not a sequence!', _seq_name;
   else
        EXECUTE format('CREATE SEQUENCE %I MINVALUE 1 INCREMENT BY 1', _seq_name);
   end case;
   RETURN nextval(_seq_name);
end
$$ language plpgsql

方案2：所有引用都使用格式化处理 #

create or replace function teenusjuhtum_generator(year integer, liik text) returns bigint as $$
declare
    _seq_name text := year || '_' || liik;
    _next_val bigint;
begin
   case (select c.relkind = 'S'::char
         from pg_catalog.pg_namespace n
         join pg_catalog.pg_class c on c.relnamespace = n.oid
         where n.nspname = current_schema()
         and c.relname = _seq_name)
   when true then
        raise notice 'Sequence % exists', _seq_name;
   when false then
         RAISE EXCEPTION '% is not a sequence!', _seq_name;
   else
        EXECUTE format('CREATE SEQUENCE %I MINVALUE 1 INCREMENT BY 1', _seq_name);
   end case;
   -- 使用EXECUTE和format正确引用序列名
   EXECUTE format('SELECT nextval(%I)', _seq_name) INTO _next_val;
   RETURN _next_val;
end
$$ language plpgsql

额外优化建议 #

• 添加IF NOT EXISTS到CREATE SEQUENCE语句中，避免并发调用时的冲突：

  EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I MINVALUE 1 INCREMENT BY 1', _seq_name);
  

• 子查询可以简化为使用pg_sequence_exists函数（PostgreSQL 11+支持）：

  case when pg_sequence_exists(format('%I.%I', current_schema(), _seq_name)) then
      raise notice 'Sequence % exists', _seq_name;
  when pg_class_exists(format('%I.%I', current_schema(), _seq_name)) then
      RAISE EXCEPTION '% is not a sequence!', _seq_name;
  else
      EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I MINVALUE 1 INCREMENT BY 1', _seq_name);
  end case;
  

内容的提问来源于stack exchange，提问作者Kalev