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

You do not want to use the data type char. That's short for character(1) and completely wrong for passing a "password" text. Any string would be truncated to the first character. The manual:

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).

Bold emphasis mine.

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

If you actually need to return TRUE / FALSE, 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)  -- 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 only considers if a row is returned. It's irrelevant whether you write NULL or FALSE or TRUE or * or nothing at all 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 (improved with input from 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$;

Answer 2

If you just add SETOF before boolean, then instead of 1 row with a NULL cell, the function will return 0 rows.

Otherwise, you could try:

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;