如果函数声明的结果表的描述(例如,field1)中有一个字段出现在括号"100"中,则在执行函数时会发生错误:
SQL Error [42702]: ERROR: column reference "field1" is ambiguous
为了不描述这个问题很长时间和难度,我立即给出了一个行为奇怪的现成测试代码的例子:
创建要插入行的表:
drop table if exists testing_table;
create TABLE testing_table (
-- Keep in mind the name of this field
field1 int4
, s1 text
, constraint pk_testing_table_field1 PRIMARY key (field1)
);
创建将行插入到表中的函数
-- drop function if exists insert_testing_table;
create or replace function insert_testing_table(p_field1 int4, p_s1 text)
returns table
(
-- Keep in mind the name of this field
field1 int4
, s1 text
)
LANGUAGE plpgsql AS $function$
begin
insert into testing_table as r (field1, s1) values (p_field1, p_s1)
on conflict (
-- It is this place that is the conflicting name that causes the error
field1
)
do update set s1 = p_s1 where r.field1 = p_field1;
return query select t.* from testing_table t where t.field1 = p_field1;
end $function$;
调用函数:
select * from insert_testing_table(1, 'aaa');
此调用将导致错误:
SQL Error [42702]: ERROR: column reference "field1" is ambiguous
不能在表达式"on conflict(...)"的括号中指定别名、表名或约束名,只能指定表字段的直接名称.
"is ambiguous"是什么意思? 在"insert on conflict do update"语句中,field1列与表的哪一列冲突?
如果我重命名"return query select..."表达式中的字段,错误仍然存在.
而是if I rename the "field1" field in the description of the resulting table, the function will run without error美元.
如何在表达式"On Conflicts(...)"的圆括号中指定字段的名称以消除错误?
我明白我可以使用:
ON CONFLICT ON CONSTRAINT
,constraint_name.
但我想通过在"on Conflicts(...)"中指定字段来解决异常情况,该字段与返回表的描述中的字段相同. 这是一种什么样的陌生?