Задайте возвращаемое значение по умолчанию для функции Postgres

У меня есть следующая функция в Postgres: 

CREATE OR REPLACE FUNCTION point_total(user_id integer, gametime date)
  RETURNS bigint AS
$BODY$
SELECT sum(points) AS result
  FROM picks
 WHERE user_id = $1
   AND picks.gametime > $2
   AND points IS NOT NULL;
$BODY$
  LANGUAGE sql VOLATILE;

Он работает правильно, но когда пользователь начинает и не имеет очков, он очень разумно возвращает NULL. Как я могу изменить его так, чтобы он возвращал 0 вместо этого.

Изменение тела функции на приведенное ниже приводит к "ошибке: синтаксическая ошибка при или около "IF".

SELECT sum(points) AS result
  FROM picks
 WHERE user_id = $1
   AND picks.gametime > $2
   AND points IS NOT NULL;

IF result IS NULL
   SELECT 0 AS result;
END;
1 3
sql postgresql function if-statement plpgsql

1 ответ:

Вам нужно изменить язык с sql на plpgsql, Если вы хотите использовать процедурные возможности PL/pgSQL. Тело функции тоже меняется.

Имейте в виду, что Все имена параметров видны в теле функции, включая все уровни инструкций SQL. Если вы создадите конфликт имен, вам может потребоваться указать в таблице имена столбцов следующим образом: table.col, чтобы избежать путаницы. Поскольку вы ссылаетесь на параметры функции по позиционной ссылке ($n), я просто удалил имена параметров, чтобы заставить его работать.

И, наконец, THEN отсутствовал в операторе IF - непосредственной причине сообщения об ошибке.

Для замены значений NULLможно использовать COALESCE . Но это работает только в том случае, если есть хотя бы одна результирующая строка. COALESCE не может исправить "нет строки", он может только заменить фактические значения NULL.

Существует несколько способов, чтобы покрыть все NULL случаях. В plpgsql функции : 

CREATE OR REPLACE FUNCTION point_total(integer, date, OUT result bigint)
  RETURNS bigint AS
$func$
BEGIN

SELECT sum(p.points)          -- COALESCE would make sense ...
INTO   result
FROM   picks p
WHERE  p.user_id = $1
AND    p.gametime > $2
AND    p.points IS NOT NULL;  -- ... if NULL values were not ruled out

IF NOT FOUND THEN             -- If no row was found ...
   result := 0;               -- ... set to 0 explicitly
END IF;

END
$func$  LANGUAGE plpgsql;

Или вы можете заключить весь запрос в выражении COALESCE во внешнем выражении SELECT. "Нет строки" из внутреннего SELECT приводит к NULL в выражении. Работайте как обычный SQL, или вы можете обернуть его в функцию sql: 

CREATE OR REPLACE FUNCTION point_total(integer, date)
  RETURNS bigint AS
$func$
SELECT COALESCE(
  (SELECT sum(p.points)
   FROM   picks p
   WHERE  p.user_id = $1
   AND    p.gametime > $2
   -- AND    p.points IS NOT NULL  -- redundant here
  ), 0)
$func$  LANGUAGE sql;

Соответствующий ответ:

Относительно конфликтов имен

Одной из проблем, скорее всего, был конфликт имен. В Версии 9.0 произошли серьезные изменения. Я цитирую: примечания к выпуску :

Е. 8.2.5. PL/pgSQL

PL/pgSQL теперь выдает ошибку, если имя переменной конфликтует с имя столбца, используемого в запросе (Tom Lane)

Более поздней версии усовершенствовали поведение. В очевидных местах правильная альтернатива выбирается автоматически. Снижает вероятность возникновения конфликтов, но она все еще существует. Этот совет по-прежнему применяется в Postgres 9.3.

10