sql - 如何在ON CONFLICT子句中消除plpgsql变量名称的歧义？

给定此表:

create table test (
    name text primary key
);

我需要编写一个plpgsql函数，其变量名与主键名冲突，必须在on conflict子句中使用该主键名:

create or replace function func(
    name text                               -- this variable name...
) returns void language plpgsql as
$$
begin
    insert into test (name) values (name)
    on conflict (name) do update            -- ...conflicts with this line
    set name = func.name;
end;
$$;

这将进行编译，但随后会抛出一个含糊的列引用:

select * from func('one');
ERROR:  column reference "name" is ambiguous
LINE 2:     on conflict (name) do update
                        ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  insert into test (name) values (name)
    on conflict (name) do update
    set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

我尝试将完整的列名指定为不编译的on conflict (test.name)或编译的((test.name)):

create or replace function func(
    name text
) returns void language plpgsql as
$$
begin
    insert into test (name) values (name)
    on conflict ((test.name)) do            -- this fails too
    update set name = func.name;
end;
$$;

但是它也失败了:

select * from func('two');
ERROR:  invalid reference to FROM-clause entry for table "test"
LINE 2:     on conflict ((test.name)) do
                          ^
HINT:  There is an entry for table "test", but it cannot be referenced from this part of the query.
QUERY:  insert into test (name) values (name)
    on conflict ((test.name)) do
    update set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

有解决方案吗？

编辑:我找到了一种解决方法:

on conflict on constraint test_pkey do update

其中test_pkey是表名加上_pkey。我不知道这有多可靠。我仍然想改为指定列名。

最佳答案

首先，对于变量和属性，name都是一个不好的名字。两者兼而有之时，代码将看起来不那么好。考虑到这一点，您可以在变量前面加上标签块(例如在<<fn>>``), and set variable_conflict`下面的示例中，以优先选择列名，请参见下面的代码:

t=# create or replace function func(
    name text
) returns void language plpgsql as
$$
#variable_conflict use_column
<<fn>>
declare name text :='blah';
begin
    insert into test (name) values (name)
    on conflict (name) do            -- this no longer fails
    update set name = fn.name;
end;
$$;
t=# insert into test select 'b';
INSERT 0 1
Time: 8.076 ms
t=# select func('b');
 func
------

(1 row)

Time: 6.117 ms
t=# select * from test;
 name
------
 b
 blah
(2 rows)

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST



进一步-基本上整个链接都与此有关。

但是，在演示了如何使用plpgsql轻松完成特定任务之后，我仍然引用namual: