如何在ON CONFLICT子句中消除plpgsql变量名称的歧义? [英] How to disambiguate a plpgsql variable name in a ON CONFLICT clause?
问题描述
给出此表:
create table test (
name text primary key
);
我需要编写一个plpgsql函数,其变量名与主键名冲突,必须在on conflict
子句中使用它:
I need to write a plpgsql function with a variable name that collides with the primary key name, which I must use in a on conflict
clause:
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;
$$;
这会编译,但是会抛出一个含糊的列引用:
This compiles, but then throws an ambiguous column reference:
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))
:
I tried specifying the full column name as on conflict (test.name)
which does not compile, or ((test.name))
which compiles:
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
有解决方案吗?
我找到了一种解决方法:
I found a workaround:
on conflict on constraint test_pkey do update
其中,test_pkey
是表名加_pkey
.我不知道这有多可靠.我仍然想改为指定列名.
where test_pkey
is the table name plus _pkey
. I don't know how reliable this is though. I'd still like to specify the column name instead.
推荐答案
首先,对于变量和属性,name
都是一个不好的名字.两者兼而有之时,代码将看起来不那么好.考虑到这一点,您可以在变量前加上带标签的块(例如在<<fn>>``), and set
variable_conflict`下面的示例中,以优先选择列名,请参见下面的代码:
to start with, name
is a bad name for both variable and attribute. When you have both, code won't look good. with that in mind, you can "prefix" variable with labeled block (in example below <<fn>>``), and set
variable_conflict` to give preference to column name, see code below:
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
默认情况下,如果SQL语句中的名称,PL/pgSQL将报告错误 可以引用变量或表列.您可以解决这样的问题 通过重命名变量或列,或通过限定 模棱两可的引用,或通过告诉PL/pgSQL对哪个解释 喜欢.
By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer.
,进一步-基本上整个链接都是关于它的.
and further - basically the whole link is about it.
但是-在演示了如何使用plpgsql轻松完成特定任务之后,我仍然引用namual:
And yet - after demonstrating how particular task this can be easily done with plpgsql, I still quote namual:
最简单的解决方案是重命名变量或列.普通的 编码规则是对PL/pgSQL使用不同的命名约定 变量,而不是用于列名的变量.例如,如果您 一致地命名函数变量v_something,而您都没有 列名称以v_开头,不会发生冲突.
The simplest solution is to rename the variable or column. A common coding rule is to use a different naming convention for PL/pgSQL variables than you use for column names. For example, if you consistently name function variables v_something while none of your column names start with v_, no conflicts will occur.
这篇关于如何在ON CONFLICT子句中消除plpgsql变量名称的歧义?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!