Set a default return value for a Postgres function
I have the following function in 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;
It works correctly, but when a user starts out and has no points, it very reasonably returns NULL. How can I modify it so that it returns 0 instead.
Changing the body of the function to tha开发者_运维百科t below results in an "ERROR: syntax error at or near "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;
You need to change the language from sql
to plpgsql
if you want to use the procedural features of PL/pgSQL. The function body changes, too.
Be aware that all parameter names are visible in the function body, including all levels of SQL statements. If you create a naming conflict, you may need to table-qualify column names like this: table.col
, to avoid confusion. Since you refer to function parameters by positional reference ($n
) anyway, I just removed parameter names to make it work.
Finally, THEN
was missing in the IF
statement - the immediate cause of the error message.
One could use COALESCE to substitute for NULL
values. But that only works if there is at least one resulting row. COALESCE
can't fix "no row" it can only replace actual NULL
values.
There are several ways to cover all NULL
cases. In plpgsql functions:
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;
Or you can enclose the whole query in a COALESCE
expression in an outer SELECT
. "No row" from the inner SELECT
results in a NULL
in the expression. Work as plain SQL, or you can wrap it in an sql function:
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;
Related answer:
- How to display a default value when no match found in a query?
Concerning naming conflicts
One problem was the naming conflict most likely. There have been major changes in version 9.0. I quote the release notes:
E.8.2.5. PL/pgSQL
PL/pgSQL now throws an error if a variable name conflicts with a column name used in a query (Tom Lane)
Later versions have refined the behavior. In obvious spots the right alternative is picked automatically. Reduces the potential for conflicts, but it's still there. The advice still applies in Postgres 9.3.
精彩评论