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

Question

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

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

Answer 1

Начать с, 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

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

и далее - в основном вся ссылка об этом.

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

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

Answer 2

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

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

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

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