PostgreSQL: Update function return boolean

boolean, postgresql, sql-returning, sql-update

Solution

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$;

Problem

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`?

Original source