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"

为什么会这样？显然我想在 select 语句遇到 a + b + c 等于 0 并返回 0 的情况时捕获。

Answer 1

EXCEPTION 子句适用于整个 BEGIN block ，一直运行到 END。也就是说，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;

将“RETURN COALESCE...”移到 EXCEPTION 行上方，然后重试。