PostgreSQL和division_by_zero异常

Question

我不太明白这一点..我的函数如下所示：

create or replace function myfunc(integer, varchar(25), varchar(25), integer, integer) returns numeric as $$
declare var_return numeric;

begin

select sum(a+ b) / sum(a + b + c)
from mytable
where col1 = $1
and col2 = $2
and col3 = $3
and col4 between $4 AND $5
into var_return;

exception when division_by_zero then return 0.0;

return coalesce(var_return, 0.0);

end;

$$ language plpgsql;

但是当我执行select myfunc(123, 'foo', 'bar', 1, 10);时，我看到：

ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function "myfunc"

这一切为什么要发生？显然，我希望在a + b + c等于0并返回0的情况下捕获select语句。

Answer 1

EXCEPTION子句与整个BEGIN块相关，并一直运行到末尾。也就是说，Postgres理解您写了以下内容：

create or replace function myfunc(...) returns ... as $$
  declare var_return numeric;

  begin
    select ...
    into var_return; -- but no RETURN statement in this block
  exception when division_by_zero then
    return 0.0;
    return coalesce(var_return, 0.0); -- this is part of the exception handler
  end;
$$ language plpgsql;

移动你的“返回联合...”，然后重试。

Answer 2

你可以极大地简化你的函数：

CREATE OR REPLACE FUNCTION myfunc(int, varchar(25), varchar(25), int, int)
  RETURNS numeric AS
$BODY$

SELECT CASE WHEN abc = 0 THEN 0::numeric ELSE (ab/abc)::numeric END
FROM (
    SELECT sum(a + b) AS ab, sum(a + b + c) AS abc
    FROM   mytable
    WHERE  col1 = $1
    AND    col2 = $2
    AND    col3 = $3
    AND    col4 BETWEEN $4 AND $5
    ) x;

$BODY$ language SQL;

要点

• 这是language SQL，不是PL/pgSQL函数。你可以使用这两种方法中的任何一种，但对于像这样简单的情况，你最好还是使用更简单的工具。
• @pilcrow准确地诊断出了错误的原因。按照您的方式，RETURN语句是异常处理程序的一部分，只有在发生异常时才会到达。
• 如果a, b, c的数据类型应为integer或bigint，则您可能希望在除法之前强制转换为numeric，否则结果将被强制转换为CASE语句，以避免被零除。这比异常处理要便宜得多。

Answer 3

你确定你处理了正确的异常吗？

我猜你还有另一个例外。

See a list of exception you can trap.

我会先试一下(我总是把异常放在代码块的末尾，这样更容易阅读。)

create or replace function myfunc(integer, varchar(25), varchar(25), integer, integer) returns numeric as $$
declare var_return numeric;

begin

   select sum(a+ b) / sum(a + b + c)
   from mytable
   where col1 = $1
   and col2 = $2
   and col3 = $3
   and col4 between $4 AND $5
   into var_return;

   return coalesce(var_return, 0.0);

   exception 
      when division_by_zero then 
         return 0.0;
      when others then 
         RAISE NOTICE 'caught others';
         return 0.0;



end;

$$ language plpgsql;