代码之家 › 专栏 › 技术社区 › Tristan

为什么在这个Postgres函数中需要显式表名?

sql-function plpgsql postgresql

0

Tristan · 技术社区 · 2 年前

我是第一次使用Postgres,我被一个关于我的函数的“模糊”错误弄糊涂了。

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;

我的问题是关于的要求 "user." 在中 SELECT 以及 WHERE 原因如果没有它,我会得到一个错误:

错误:列引用“user_id”不明确

歧义在哪里?它和 user_id 在中 RETURNS TABLE 条款

1 回复 | 直到 2 年前

1

0

Erwin Brandstetter 2 年前

歧义在哪里?它和 user_id 在中 RETURNS TABLE 条款

是的,此处列出的所有列都算作 OUT 参数,这些参数在函数内部的任何SQL查询中都可见,因此与列名冲突,除非限定。看见

也就是说,您的功能可以简单得多:

CREATE OR REPLACE FUNCTION user_get(user_id_param text = NULL, phone_number_param text = NULL, username_param text = NULL)
  RETURNS SETOF "user"
  LANGUAGE sql AS
$func$
SELECT *
FROM   "user" u
WHERE  u.user_id = user_id_param
   OR  u.phone_number = phone_number_param
   OR  u.username = username_param;
$func$;

一个更简单的SQL函数可以做到这一点。如果你需要一个函数的话。

您的返回类型与表的行类型完全匹配,因此您可以使用它。 RETURNS SETOF "user" 而不是 RETURNS TABLE (...) (请注意,这引入了对表类型的函数依赖!) 而且删除观察到的冲突。这是一个罕见的案例 SELECT * 当从函数返回时是有意义的,因为返回类型与每个定义的行类型相匹配。

如果必须使用表限定列名(从一开始就不是个坏主意!),请考虑使用短列别名以提高可读性( u 在我的情况下)。

我怀疑你是否需要默认值 NULL 对于 IN 参数。

而且你不需要检查 null 此外。使用相等运算符进行检查 = 排除 无效的 隐含地,作为 无效的 与任何回报相比 无效的 从不 true 看见

Best way to check for "empty or null value"

另外,只是不要完全使用 reserved word 喜欢 user 作为标识符。