PostgreSQL函数返回表字段与ON CONFLICT字段重名引发歧义错误排查

1. 歧义错误的具体原因 #

PL/pgSQL函数中，返回表定义的列（此处为field1）会被视为当前函数作用域内的标识符。当你在ON CONFLICT(field1)子句中使用field1时，PostgreSQL解析器同时识别到两个同名的标识符：一个是函数返回表的field1列，另一个是目标表testing_table的field1字段。由于ON CONFLICT(...)要求直接指定字段名，不允许通过表名或别名限定，数据库无法判断你要引用的是哪一个，因此抛出ERROR: column reference "field1" is ambiguous错误。

2. 无侵入解决方案（不修改返回表字段名、不使用约束名） #

使用动态SQL执行INSERT操作即可消除歧义。动态SQL的解析过程独立于函数的当前作用域，不会受到返回表列名的干扰，因此其中的field1会明确指向目标表testing_table的字段。

修改后的函数代码如下：

create or replace function insert_testing_table(p_field1 int4, p_s1 text)
returns table 
(
  field1 int4
  , s1 text
)
LANGUAGE plpgsql AS $function$
begin 
  -- 用动态SQL执行INSERT，规避作用域内的标识符冲突
  execute 'insert into testing_table as r (field1, s1) values ($1, $2)
           on conflict (field1)
           do update set s1 = $2 where r.field1 = $1'
  using p_field1, p_s1;

  return query select t.* from testing_table t where t.field1 = p_field1;
end $function$;

调用该函数即可正常执行，不会再触发歧义错误。

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