PostgreSQL 9.5：异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40802589/
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
PostgreSQL 9.5: Exception handling
提问by MAK
I have table called employee with two columns, and have created two functions for insertion and updation operations. These two function will be called through another function which named as udf_3().
我有一个名为employee 的表，有两列，并为插入和更新操作创建了两个函数。这两个函数将通过另一个名为 udf_3() 的函数调用。
I want to do exception handling on the third function that is udf_3()which should
give me the details of which function has error.
我想对第三个函数进行异常处理，udf_3()它应该给我哪个函数有错误的详细信息。
--Table: employee
--表：employee
create table employee
(
id int,
name varchar(10)
);
--Function1: udf_1()used for insertion.
--功能一：udf_1()用于插入。
create or replace function udf_1()
returns void as
$body$
begin
insert into employee values(1,'Mak');
end;
$body$
language plpgsql;
--Function2: udf_2()used for updation.
--功能二：udf_2()用于更新。
create or replace function udf_2()
returns void as
$body$
begin
update employee
set a_id = 99
where name = 'Mak';
end;
$body$
language plpgsql;
--Function3: udf_3()used to call all above function.
--函数3：udf_3()用于调用以上所有函数。
create or replace function udf_3()
returns int as
$body$
begin
perform udf_1();
perform udf_2();
return 0;
exception
when others then
RAISE INFO 'Error Name:%',SQLERRM;
RAISE INFO 'Error State:%', SQLSTATE;
return -1;
end;
$body$
language plpgsql;
--Function Calling:
--函数调用：
select * from udf_3();
Exception:
例外：
INFO: Error Name:column "a_id" of relation "employee" does not exist
INFO: Error State:42703
Problem: I am able to get the exception BUTnot able to get from which function i got exception.
问题：我能够得到异常但无法从我得到异常的函数中得到。
回答by AlexM
According to the documentation
根据文档
Within an exception handler, one may also retrieve information about the current exception by using the
GET STACKED DIAGNOSTICScommand
在异常处理程序中，还可以使用以下
GET STACKED DIAGNOSTICS命令检索有关当前异常的信息
Example:
例子：
create or replace function udf_3()
returns int as
$body$
declare
err_context text;
begin
perform udf_1();
perform udf_2();
return 0;
exception
when others then
GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
RAISE INFO 'Error Name:%',SQLERRM;
RAISE INFO 'Error State:%', SQLSTATE;
RAISE INFO 'Error Context:%', err_context;
return -1;
end;
$body$
language plpgsql;
will display the following:
将显示以下内容：
INFO: Error Context:SQL: "SELECT udf_1()"
But this is just a textual representation of the error. Your logic should not rely on it. It is better to use custom error codes to handle exception logic (and raise meaningful exceptions in your functions that you can catch and handle later on).
但这只是错误的文本表示。你的逻辑不应该依赖它。最好使用自定义错误代码来处理异常逻辑（并在您的函数中引发有意义的异常，以便您稍后捕获和处理）。
UPDATE:
更新：
Another solution is to separate your code in different blocks for which you can catch exceptions separately. In this case you know from which block the exception was raised:
另一种解决方案是将您的代码分隔在不同的块中，您可以分别捕获异常。在这种情况下，您知道从哪个块引发异常：
DO $$
BEGIN
-- Block 1
BEGIN
-- any code that might raise an exception
RAISE EXCEPTION 'Exception 1'; -- for example
EXCEPTION
WHEN others THEN
RAISE INFO 'Caught in Block 1';
END;
-- Block 2
BEGIN
-- any code that might raise an exception
RAISE EXCEPTION 'Exception 2'; -- for example
EXCEPTION
WHEN others THEN
RAISE INFO 'Caught in Block 2';
END;
END $$
