PostgreSQL: функция обновления возвращает логическое значение

Question

Функция ниже хороша?

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

Возвращается TRUE когда UPDATE наборы password но NULL (вместо FALSE) когда UPDATE не устанавливается password,

Я думаю, что это будет работать для всех намерений и целей, но вы думаете, что все в порядке?

Если нет, как бы вы изменили функцию для возврата FALSE (вместо NULL) если UPDATE не устанавливается password?

Answer 1

Прежде всего, вы не хотите использовать тип данных char, Это синоним character(1) и совершенно неправильно для передачи текста "пароль". Любая строка будет обрезана до первого символа. По документации:

Обозначения varchar(n) а также char(n) псевдонимы для character varying(n) а также character(n) соответственно. character без спецификатора длины эквивалентно character(1),

Далее, что не так с функцией, возвращающей TRUE или же NULL?

Если вам действительно нужно TRUE / FALSE вернулся, ваша идея с использованием CTE, модифицирующей данные, работает. Однако код вводит в заблуждение. Вы делаете это похоже на TRUE в конечном итоге SELECT будет иметь значение, но это не так:

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 учитывает только, если строка возвращается. Неважно, пишешь ли ты NULL или же FALSE или же TRUE или же * или же 'foo' или что угодно. Функция, возвращающая TRUE только говорит нам, UPDATE вернул одну или несколько строк.

Альтернативой может быть функция PL/pgSQL, использующая специальную переменную 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;

Немного быстрее и, наверное, понятнее. Или, как прокомментировал @pozs, так как мы возвращаемся boolean в любом случае в этом случае просто:

   RETURN FOUND;

Answer 2

Если вы просто добавите SETOF до booleanзатем вместо 1 строки с NULL ячейка, функция вернет 0 строк.

В противном случае вы можете попробовать:

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;