PostgreSQL UPSERT函数报错：ON CONFLICT中title引用歧义如何解决？

问题背景 #

创建了public.publications表，编写PL/pgSQL函数public.upsert_publication实现UPSERT功能时，调用函数报错：

ERROR: column reference "title" is ambiguous
系统提示title既可能是函数的输入参数，也可能是表的列，无法区分。尝试在ON CONFLICT子句中给列名添加表名前缀时，又触发了语法错误。

（注：最终通过修改输入参数名解决了问题，以下补充其他可行方案）

问题根源 #

当PL/pgSQL函数的输入参数名与表的列名完全一致时（比如这里的title），PostgreSQL无法判断代码中引用的title是函数参数还是表列，因此抛出歧义错误。另外，ON CONFLICT子句中直接使用表名.列名的写法不符合语法规范，会触发新的错误。

几种解决办法 #

1. 修改输入参数名（最直接的可行方案） #

给函数的输入参数改名，避开与表列重复的名称，比如给参数加p_前缀：

CREATE OR REPLACE FUNCTION public.upsert_publication(
    p_title text,
    -- 其他参数...
) RETURNS void AS $$
BEGIN
    INSERT INTO public.publications(title, ...)
    VALUES(p_title, ...)
    ON CONFLICT (title) DO UPDATE
    SET title = p_title; -- 参数与表列名称区分开，无歧义
END;
$$ LANGUAGE plpgsql;

2. 使用NEW关键字指代插入的参数值 #

如果不想修改参数名，在ON CONFLICT DO UPDATE的SET子句中，用NEW.title表示本次INSERT要插入的参数值，与表列区分：

CREATE OR REPLACE FUNCTION public.upsert_publication(
    title text,
    -- 其他参数...
) RETURNS void AS $$
BEGIN
    INSERT INTO public.publications(title, ...)
    VALUES(title, ...)
    ON CONFLICT (title) DO UPDATE
    SET title = NEW.title; -- NEW指代本次插入的行数据，明确是参数值
END;
$$ LANGUAGE plpgsql;

3. 用函数名限定参数引用 #

直接通过函数名明确指定引用的是函数的输入参数：

CREATE OR REPLACE FUNCTION public.upsert_publication(
    title text,
    -- 其他参数...
) RETURNS void AS $$
BEGIN
    INSERT INTO public.publications(title, ...)
    VALUES(title, ...)
    ON CONFLICT (title) DO UPDATE
    SET title = upsert_publication.title; -- 明确指定为函数的输入参数
END;
$$ LANGUAGE plpgsql;

4. 给表添加别名区分列 #

在INSERT语句中给表设置别名，需要引用表列时用别名明确指定：

CREATE OR REPLACE FUNCTION public.upsert_publication(
    title text,
    -- 其他参数...
) RETURNS void AS $$
BEGIN
    INSERT INTO public.publications AS pub(title, ...)
    VALUES(title, ...)
    ON CONFLICT (title) DO UPDATE
    SET title = title; -- 此处PostgreSQL优先识别为参数，若要表列则写pub.title
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者Rune