postgresql 处理异常并从函数返回结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16985757/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Handling EXCEPTION and return result from function
提问by Oto Shavadze
This is my code
这是我的代码
CREATE OR REPLACE FUNCTION test_excep (arg INTEGER) RETURNS INTEGER
AS $$
DECLARE res INTEGER;
BEGIN
res := 100 / arg;
BEGIN
EXCEPTION
WHEN division_by_zero
THEN RETURN 999;
END;
RETURN res;
END;
$$
LANGUAGE plpgsql;
That is, I need returned "999", if happened division by zero, but this: SELECT test_excep(0)
也就是说，如果发生被零除，我需要返回“999”，但是这个： SELECT test_excep(0)
returns error: division by zero
CONTEXT: PL/pgSQL function test_excep(integer) line 4 at assignment
返回错误： division by zero
CONTEXT: PL/pgSQL function test_excep(integer) line 4 at assignment
What is wrong in my code?
我的代码有什么问题？
回答by Erwin Brandstetter
The EXCEPTIONclause needs to be in the same block as the exception.
该EXCEPTION子句需要与异常位于同一块中。
For instance:
例如：
CREATE OR REPLACE FUNCTION test_excep (arg integer)
RETURNS integer
AS
$func$
DECLARE
res INTEGER;
BEGIN
res := 100 / arg;
RETURN res;
EXCEPTION
WHEN division_by_zero
THEN RETURN 999;
END
$func$
LANGUAGE plpgsql;
回答by Igor Romanchenko
Here is a correct function:
这是一个正确的函数：
CREATE OR REPLACE FUNCTION test_excep (arg INTEGER) RETURNS INTEGER
AS $$
DECLARE res INTEGER;
BEGIN
res := 100 / arg;
RETURN res;
EXCEPTION
WHEN division_by_zero
THEN RETURN 999;
END;
$$
LANGUAGE plpgsql;
EXCEPTIONpart must be inside the block where the exception is, and also must be the last part of the block.
EXCEPTION部分必须在异常所在的块内，也必须是块的最后一部分。
