Как устранить неоднозначность имени переменной plpgsql в предложении ON CONFLICT?

Учитывая эту таблицу: 

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. Хотя я не знаю, насколько это надежно. Я все равно хотел бы указать имя столбца.

2 3
sql postgresql plpgsql upsert

2 ответа:

Начнем с того, что name - это плохое имя как для переменной, так и для атрибута. Когда у вас есть и то, и другое, код не будет выглядеть хорошо. имея это в виду, вы можете "префиксировать" переменную с помеченным блоком (в примере ниже <<fn>>``), and setvariable_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

По умолчанию PL/pgSQL сообщает об ошибке, если имя в инструкции SQL могли относиться либо к переменной, либо к столбцу таблицы. Вы можете исправить такое проблема путем переименования переменной или столбца, или путем уточнения неоднозначная ссылка, или, говоря на языке PL/список pgsql, который толкование предпочитать.

И далее-в основном вся связь об этом.

И все же-после демонстрации того, как конкретная задача может быть легко выполнена с plpgsql, я все еще цитирую namual:

Самое простое решение-переименовать переменную или столбец. Обычное явление правило кодирования это использовать другое соглашение об именовании для PL/pgSQL переменные, чем вы используете для имен столбцов. Например, если вы последовательно назовите переменные функции v_something, пока ни один из ваших имена столбцов начинаются с v_, никаких конфликтов не возникнет.
3

СинтаксисON CONFLICT... (как описано здесь ) используетуникальное ограничение , чтобы определить, конфликтует ли строка. Вы можете указать это уникальное ограничение, либо перечисляя столбцы, которые оно содержит (в этот момент Postgres "выводит" правильный индекс для использования), либо непосредственно называя ограничение.

В вашем случае используется уникальное ограничение-это ограничение первичного ключа, неявно созданное во время выполнения оператора CREATE TABLE. Это будет иметь имя, данное ему СУБД, если только вы указываете его непосредственно; поэтому вам нужно будет либо найти имя, которое дала ему СУБД (и знать, что это может измениться, если вы воссоздадите схему позже), либо назвать его явно при создании таблицы с использованием синтаксиса CONSTRAINT pk_some_name PRIMARY KEY.

Затем следует указать предложение как ON CONFLICT ON CONSTRAINT pk_some_name DO ... (Обратите внимание, что имя ограничения не заключено в скобки).

(в качестве альтернативы, конечно, вы можете изменить свою функцию, чтобы использовать однозначное имя параметра; лично я думаю, что это хорошая практика использовать префикс например, p_ или in_ вместо того, чтобы решать конфликты на индивидуальной основе.)

1