为Postgres函数设置默认返回值

Question

为Postgres函数设置默认返回值

sqlfunctionpostgresqlif-statementplpgsql

5

我在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;

- Mike Buckbee

请问使用的 PostgreSQL 版本是多少？我猜是 8.4？ - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

如果您想要使用PL/pgSQL的过程特性，您需要将语言从sql更改为plpgsql。函数主体也会发生变化。

请注意，在函数主体中所有参数名称都是可见的，包括所有级别的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 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;

或者您可以将整个查询语句放在外部 SELECT 中的 COALESCE 表达式中。内部 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版本中有重大变化。我引用发布说明：

E.8.2.5. PL/pgSQL

如果一个变量名与查询中使用的列名冲突，PL/pgSQL现在会抛出错误 (Tom Lane)

后续版本已经完善了这种行为。在明显的情况下，适当的替代方案会被自动选择。这减少了冲突的潜力，但仍然存在。在Postgres 9.3中仍然适用这个建议。