如何解决PostgreSQL PL/pgSQL函数中INSERT ON CONFLICT的列引用歧义错误？

错误原因 #

报错column reference "email" is ambiguous是因为你定义的函数返回表中包含email字段，和目标表email_verifications的email列同名，PL/pgSQL无法区分这个email是返回表的输出变量，还是原表的列。而在函数外部执行相同SQL时，没有返回表的变量冲突，所以不会触发这个错误。

解决方案 #

方案1：修改返回表字段名，避免重名 #

给返回表的每个字段添加前缀（比如out_），彻底消除命名冲突：

create function create_email_verification(
    p_email text, 
    p_code text, 
    p_expires_at timestamp with time zone)

returns TABLE(
    out_id integer, 
    out_email text, 
    out_code text, 
    out_expires_at timestamp with time zone, 
    out_created_at timestamp with time zone
) language plpgsql
as
$$
BEGIN
    RETURN QUERY
        INSERT INTO email_verifications (email, code, expires_at)
        VALUES (p_email, p_code, p_expires_at)
        ON CONFLICT (email)
        DO UPDATE 
        SET
            code = EXCLUDED.code, 
            expires_at = EXCLUDED.expires_at
        RETURNING 
            email_verifications.id,
            email_verifications.email, 
            email_verifications.code,
            email_verifications.expires_at,
            email_verifications.created_at;
END;
$$;

方案2：在RETURNING子句中显式指定别名 #

如果需要保持返回表的字段名不变，就在RETURNING时给每个表列加上和返回表字段完全一致的别名，明确告诉PL/pgSQL映射关系：

create function create_email_verification(
    p_email text, 
    p_code text, 
    p_expires_at timestamp with time zone)

returns TABLE(
    id integer, 
    email text, 
    code text, 
    expires_at timestamp with time zone, 
    created_at timestamp with time zone
) language plpgsql
as
$$
BEGIN
    RETURN QUERY
        INSERT INTO email_verifications (email, code, expires_at)
        VALUES (p_email, p_code, p_expires_at)
        ON CONFLICT (email)
        DO UPDATE 
        SET
            code = EXCLUDED.code, 
            expires_at = EXCLUDED.expires_at
        RETURNING 
            email_verifications.id AS id,
            email_verifications.email AS email, 
            email_verifications.code AS code,
            email_verifications.expires_at AS expires_at,
            email_verifications.created_at AS created_at;
END;
$$;

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