PostgreSQL：更新函数返回布尔值

Question

下面的函数好吗？

CREATE FUNCTION password_set(bigint, char) RETURNS boolean AS $$
   UPDATE users SET password = $2 WHERE id = $1 RETURNING TRUE;
$$ LANGUAGE SQL;

当 UPDATE 设置了 password 时，它会返回 TRUE，但是当 UPDATE 没有设置 password 时，它会返回 NULL（而不是 FALSE）。

我认为这对大多数情况都可以使用，但您认为这样可以吗？

如果不行，您会如何更改函数以在 UPDATE 没有设置 password 时返回 FALSE（而不是 NULL）？

- ma11hew28

---

id是唯一的还是可能有多个受影响的行？另外，您使用的Postgres版本是什么？ - Erwin Brandstetter

Answer 1

您不应该使用数据类型char。它是character(1)的简写，对于传递“密码”文本来说完全错误。任何字符串都会被截断为第一个字符。 参考手册:

符号varchar(n)和char(n)是character varying(n)和character(n)的别名，character没有长度限定符与character(1)等效。

加粗强调是我的。

接下来，一个返回TRUE或NULL的函数有什么问题吗？

如果您确实需要返回TRUE/FALSE，使用数据修改CTE的想法是可行的。然而，这段代码可能会引起误解。你让它看起来像最终的SELECT中的TRUE很重要，但实际上并不重要：

CREATE FUNCTION password_set(bigint, text)  -- not char!
  RETURNS boolean
  LANGUAGE sql AS
$func$
   WITH u AS (
      UPDATE users
      SET    password = $2
      WHERE  id = $1
      RETURNING 1
      )
   SELECT EXISTS (SELECT FROM u);
$func$;

EXISTS 函数只考虑是否返回了至少一行数据。写入 NULL 或 FALSE 或 TRUE 或 * 或不写任何东西都是无关紧要的。函数返回 TRUE 只告诉我们，UPDATE 返回了一个或多个行。

替代方案 是使用 特殊变量 FOUND 的 PL/pgSQL 函数（结合 posz 提供的 输入 进行改进）：

CREATE OR REPLACE FUNCTION password_set(bigint, text)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE users SET password = $2 WHERE id = $1;
   RETURN FOUND;
END
$func$;

- Erwin Brandstetter

---

也许将RETURN FOUND改为FOUND会更清晰，因为FOUND已经是一个BOOLEAN变量了。 - pozs

Answer 2

如果只是在 boolean 前添加 SETOF，那么函数将返回 0 行而不是带有 NULL 单元格的 1 行。

否则，您可以尝试：

CREATE FUNCTION password_set(bigint, char) RETURNS boolean AS $$
   WITH u AS (UPDATE users SET password = $2 WHERE id = $1 RETURNING TRUE)
   SELECT EXISTS (SELECT TRUE FROM u);
$$ LANGUAGE SQL;

- ma11hew28

---

你的样例中没有SETOF。 - alphadogg