PostgreSQL函数中为何必须显式指定表名？

我首次使用PostgreSQL，编写函数时遇到了「列引用歧义」错误。

创建表的SQL语句如下：

CREATE TABLE "user" (
    user_id VARCHAR(40) PRIMARY KEY,
    email VARCHAR(254),
    phone_number VARCHAR(15) NOT NULL,
    username VARCHAR(15) NOT NULL,
    display_name VARCHAR(15),
    rank_points INT NOT NULL,
    created_on TIMESTAMP DEFAULT (TIMEZONE('UTC', NOW()))
);

我编写了用于通过ID、手机号或用户名查询用户的函数：

CREATE OR REPLACE FUNCTION user_get(user_id_param VARCHAR(40) DEFAULT NULL, phone_number_param VARCHAR(15) DEFAULT NULL, username_param VARCHAR(15) DEFAULT NULL)
RETURNS TABLE(user_id VARCHAR(40), email VARCHAR(254), phone_number VARCHAR(15), username VARCHAR(15), display_name VARCHAR(15), rank_points INT, created_on TIMESTAMP) AS
$$
BEGIN
    RETURN QUERY
    SELECT "user".user_id, "user".email, "user".phone_number, "user".username, "user".display_name, "user".rank_points, "user".created_on
    FROM "user"
    WHERE 
    (user_id_param IS NOT NULL AND "user".user_id = user_id_param) OR
    (phone_number_param IS NOT NULL AND "user".phone_number = phone_number_param) OR
    (username_param IS NOT NULL AND "user".username = username_param);
END;
$$
LANGUAGE plpgsql;

我的疑问是：为何SELECT和WHERE子句中必须添加"user".前缀？去掉前缀后会报错：

ERROR: column reference "user_id" is ambiguous

该歧义来自何处？是否与RETURNS TABLE子句中的user_id有关？

问题原因解析 #

这个歧义确实和RETURNS TABLE子句定义的列名直接相关。在PL/pgSQL函数中，RETURNS TABLE里声明的每一列（比如user_id、email等）都会成为函数内部的变量，这些变量的作用域覆盖整个函数体。

当你在SELECT或WHERE子句中直接写user_id时，PostgreSQL无法区分你指的是：

• 表"user"中的user_id列，还是
• RETURNS TABLE声明的user_id变量

这种命名冲突就导致了「列引用歧义」错误。

解决方式说明 #

添加"user".前缀后，相当于明确告诉PostgreSQL：你要引用的是"user"表中的user_id列，而非返回表的变量，这样就消除了歧义。

另外，你也可以通过给表设置别名来简化写法，比如：

CREATE OR REPLACE FUNCTION user_get(user_id_param VARCHAR(40) DEFAULT NULL, phone_number_param VARCHAR(15) DEFAULT NULL, username_param VARCHAR(15) DEFAULT NULL)
RETURNS TABLE(user_id VARCHAR(40), email VARCHAR(254), phone_number VARCHAR(15), username VARCHAR(15), display_name VARCHAR(15), rank_points INT, created_on TIMESTAMP) AS
$$
BEGIN
    RETURN QUERY
    SELECT u.user_id, u.email, u.phone_number, u.username, u.display_name, u.rank_points, u.created_on
    FROM "user" u
    WHERE 
    (user_id_param IS NOT NULL AND u.user_id = user_id_param) OR
    (phone_number_param IS NOT NULL AND u.phone_number = phone_number_param) OR
    (username_param IS NOT NULL AND u.username = username_param);
END;
$$
LANGUAGE plpgsql;

这里给"user"表设置别名u，用u.user_id明确指定表列，同样能解决歧义问题。

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