设置Postgres函数的默认返回值 [英] Set a default return value for a Postgres function
问题描述
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点不是NULL;
$ BODY $
LANGUAGE sql VOLATILE;
它可以正常工作，但是当用户启动并且没有点时，它会非常合理地返回NULL。我怎样才能修改它，以便它返回0。
将函数主体更改为下面的内容会导致IF处或附近出现ERROR：语法错误。
SELECT sum（points）AS result
FROM picks
WHERE user_id = $ 1
AND picks.gametime> $ 2
AND点不为空;
IF结果IS NULL
SELECT 0 AS结果;
END;
您需要将语言从 sql 更改为 plpgsql 如果你想使用PL / pgSQL的过程特征，函数体也会改变。
请注意所有参数名称在函数体中可见，包括所有级别的SQL语句。如果您创建了命名冲突，则可能需要按如下方式对列名进行表格限定： table.col ，以避免混淆，因为无论如何你都通过位置引用（ $ n ）引用函数参数，我只是删除了参数名称以使其工作。 >
最后，在 IF 语句中缺少 THEN - <错误信息的直接原因。
可以使用 COALESCE 来替代 NULL 值。但是，只有至少有一个结果行时才有效。 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会有意义...
INTO结果
FROM picks p
WHERE p.user_id = $ 1
and p.gametime> $ 2
和p.points不是NULL; - ...如果不排除NULL值
IF NOT FOUND THEN - 如果没有找到行...
result：= 0; - ...显式设置为0
END IF;
END
$ func $ LANGUAGE plpgsql;
或者您可以将整个查询放入 COALESCE 表达式在外部 SELECT 中。内部 SELECT 中的No row导致表达式中的 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不是NULL - 这里是多余的
），0）
$ func $ LANGUAGE SQL;
相关答案：
一个问题很可能是命名冲突。在 9.0版中发生了重大变化。我引用发行说明：
E.8.2.5。 PL / pgSQL
如果变量名称与查询中使用的
列名称冲突（Tom Lane），则PL / pgSQL现在会抛出错误
后来的版本改进了这种行为。在明显的地方，正确的选择是自动选取的。减少冲突的可能性，但仍然存在。该建议仍然适用于Postgres 9.3。
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 that 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 sqlto 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:
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.
这篇关于设置Postgres函数的默认返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
