PostgreSQL: Update function return boolean

Question

Is the function below good?

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

It returns TRUE when UPDATE sets password but NULL (instead of FALSE) when UPDATE doesn't set 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 UPDATE doesn't set password?

Answer 1

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

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

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

If you actually need TRUE / FALSE returned, your idea using a data-modifying CTE works. However, the code is misleading. You make it seem like TRUE in the final SELECT would matter, but it doesn't:

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

EXISTS only considers if a row is returned. It's irrelevant whether you write NULL or FALSE or TRUE or * or 'foo' or whatever. The function returning TRUE only tells us, the UPDATE returned one or more rows.

Alternative would be a PL/pgSQL function using the special variable FOUND:

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

   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 boolean anyway in this case, just:

   RETURN FOUND;