PostgreSQL：更新函数返回布尔值

PostgreSQL: Update function return boolean

提问by ma11hew28

Is the function below good?

下面的功能好吗？

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

It returns TRUEwhen UPDATEsets passwordbut NULL(instead of FALSE) when UPDATEdoesn't set password.

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

I think that will work for all intents and purposes, but do you think that's OK?

我认为这适用于所有意图和目的，但您认为可以吗？

If not, how would you change the function to return FALSE(instead of NULL) if the UPDATEdoesn't set password?

如果没有，你会如何改变函数返回FALSE（代替NULL），如果UPDATE没有设置不password？

回答by Erwin Brandstetter

First of all, you do notwant to use the data type char. That's synonymous for character(1)and completely wrongfor passing a "password" text. Any string would be truncated to the first character. Per documentation:

首先，你不希望使用的数据类型char。这是传递“密码”文本的同义词，character(1)也是完全错误的。任何字符串都将被截断为第一个字符。根据文档：

The notations varchar(n)and char(n)are aliases for character varying(n)and character(n), respectively. characterwithout length specifier is equivalent to character(1).

该符号varchar(n)和char(n)是别名character varying(n)和character(n)分别。character没有长度说明符等效于character(1).

Next, what's wrong with a function returning TRUEor NULL?

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

If you actually needTRUE/ FALSEreturned, your idea using a data-modifying CTEworks. However, the code is misleading. You make it seem like TRUEin the final SELECT would matter, but it doesn't:

如果您确实需要TRUE/FALSE返回，则使用数据修改 CTE 的想法可行。但是，该代码具有误导性。你让它看起来TRUE在最后的 SELECT 中很重要，但它并不重要：

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

EXISTSonly considers if a row is returned. It's irrelevant whether you write NULLor FALSEor TRUEor *or 'foo'or whatever. The function returning TRUEonly tells us, the UPDATEreturned one or more rows.

EXISTS只考虑是否返回一行。无论你写这无关紧要NULL或FALSE或TRUE或*或'foo'或什么的。返回的函数TRUE只告诉我们，UPDATE返回的一行或多行。

Alternativewould be a PL/pgSQLfunction using the special variable FOUND:

替代方法是使用特殊变量的PL/pgSQL函数：FOUND

CREATE OR REPLACE FUNCTION password_set(bigint, text)
  RETURNS boolean AS
$func$
BEGIN
   UPDATE users SET password =  WHERE id = ;

   IF FOUND THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END
$func$ LANGUAGE plpgsql;

A bit faster and probably clearer. Or, as @pozs commented, since we return booleananyway in this case, just:

快一点，可能更清晰。或者，正如@pozs 评论的那样，因为boolean在这种情况下我们无论如何都会返回，只需：

   RETURN FOUND;

回答by ma11hew28

If you just add SETOFbefore boolean, then instead of 1 row with a NULLcell, the function will return 0 rows.

如果您只是添加SETOFbefore boolean，则NULL该函数将返回 0 行而不是 1 行和一个单元格。

Otherwise, you could try:

否则，您可以尝试：

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