PostgreSQL 16.1 plpgsql函数插入报错问题排查与修正请求

问题背景 #

在Debian 12.2系统上运行PostgreSQL 16.1，编写的ref.lookup_xxx函数执行时报错，但手动插入相同数据可成功，测试用DO块也无法正常运行。

原PL/pgSQL函数 #

CREATE OR REPLACE FUNCTION ref.lookup_xxx(
    in_code character varying,
    in_description character varying)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
  declare id_val integer;
  begin
    if in_code is null then -- nothing to do
      return null;
    end if;
    -- check if code is already present in the table:
    id_val = (select min(id) from ref.xxx where code = in_code);
    if id_val is null then -- insert new code, desc into reference table:
      insert into ref.xxx (code, description) values (in_code, in_description) returning id_val;
    end if;
    return id_val; -- return id of new or existing row
  exception
    when others then
      raise exception 'lookup_xxx error code, desc = %, %', in_code, in_description;
  end; 
$BODY$;

执行错误信息 #

ERROR:  lookup_xxx error code, desc = 966501, <NULL>
CONTEXT:  PL/pgSQL function ref.lookup_xxx(character varying,character varying) line 15 at RAISE 

SQL state: P0001

手动插入成功语句 #

insert into ref.xxx (code, description) values ('966501', null);

测试用DO块（无法运行） #

do $$
declare x integer;
begin
  insert into ref.xxx (code, description) values ('966501', null) returning x;
  raise notice 'x is %', x;
end; 
$$

问题原因与修复 #

核心错误在于INSERT...RETURNING的语法使用错误：原函数中returning id_val试图返回局部变量而非表字段，正确写法是返回表中的id字段，再通过INTO子句将值赋值给变量id_val。

修复后的函数 #

CREATE OR REPLACE FUNCTION ref.lookup_xxx(
    in_code character varying,
    in_description character varying)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
  declare id_val integer;
  begin
    if in_code is null then -- nothing to do
      return null;
    end if;
    -- check if code is already present in the table:
    id_val = (select min(id) from ref.xxx where code = in_code);
    if id_val is null then -- insert new code, desc into reference table:
      insert into ref.xxx (code, description) values (in_code, in_description) returning id into id_val;
    end if;
    return id_val; -- return id of new or existing row
  exception
    when others then
      raise exception 'lookup_xxx error code, desc = %, %', in_code, in_description;
  end; 
$BODY$;

补充说明 #

• RETURNING子句后必须跟表中实际存在的字段，若要将返回值存入局部变量，需搭配INTO子句指定变量。
• 原测试DO块同样存在语法错误，修复后可正常运行：

do $$
declare x integer;
begin
  insert into ref.xxx (code, description) values ('966501', null) returning id into x;
  raise notice 'x is %', x;
end; 
$$

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