Postgresql中plpgsql异常处理方法与实例（RAISE EXCEPTION）_postgresql exception

相关
《Postgresql中plpgsql异常处理方法与实例（RAISE EXCEPTION）》
《Postgresql源码（80）plpgsql中异常处理编译与执行流程分析（sqlstate）》

Postgresql中有关plpgsql异常处理的一切（RAISE EXCEPTION）

1 抛出异常

主要列出实例，语法比较简单

语法

现在PL中支持使用RAISE语法抛出异常，具体支持下面五种语法：

1 RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
2 RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
3 RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
4 RAISE [ level ] USING option = expression [, ... ];
5 RAISE ; -- 特殊，只能在EXCEPTION语法块中使用，重新抛出catch的异常。
1
2
3
4
5

• level
  • 可选DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
  • 其中DEBUG到WARNING都不会产生错误，只会打印日志，日志级别按level输出，由log_min_messages、client_min_messages参数决定是否输出。
  • 其中EXCEPTION会产生错误，中断程序执行，错误如果不被捕获会被抛到上一层。
• 抛出指定类型的异常
  • 方式一：RAISE SQLSTATE
  • 方式二：RAISE condition_name
  • 方式三：RAISE 'text' USING ERRCODE = 'unique_violation'

抛出异常的实例展示

文本拼接

do $g$
DECLARE
  v_job_id int := 100;
BEGIN
  RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
END;
$g$;
1
2
3
4
5
6
7

执行结果

postgres=# do $g$
postgres$# DECLARE
postgres$#   v_job_id int := 100;
postgres$# BEGIN
postgres$#   RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
postgres$# END;
postgres$# $g$;
NOTICE:  Calling cs_create_job(100)
DO
1
2
3
4
5
6
7
8
9

使用Hint

级别可任选，这里使用EXCEPTION没人处理，所以抛到顶层报错。

do $g$
DECLARE
  user_id int := 100;
BEGIN
  RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';
END;
$g$;
1
2
3
4
5
6
7
8

执行结果

postgres=# do $g$
postgres$# DECLARE
postgres$#   user_id int := 100;
postgres$# BEGIN
postgres$#   RAISE EXCEPTION 'Nonexistent ID --> %', user_id
postgres$#       USING HINT = 'Please check your user ID';
postgres$# END;
postgres$# $g$;
ERROR:  Nonexistent ID --> 100
HINT:  Please check your user ID
CONTEXT:  PL/pgSQL function inline_code_block line 5 at RAISE
1
2
3
4
5
6
7
8
9
10
11

抛出指定类型异常

1

do $g$
DECLARE
  user_id int := 100;
BEGIN
  RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
END;
$g$;

-- ERROR:  Duplicate user ID: 100
-- CONTEXT:  PL/pgSQL function inline_code_block line 5 at RAISE
1
2
3
4
5
6
7
8
9
10

2

do $g$
DECLARE
  user_id int := 100;
BEGIN
  RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
END;
$g$;

-- ERROR:  Duplicate user ID: 100
-- CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE
1
2
3
4
5
6
7
8
9
10

3

do $g$
BEGIN
  RAISE division_by_zero;
END;
$g$;

-- ERROR:  division_by_zero
-- CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE
1
2
3
4
5
6
7
8

4

do $g$
BEGIN
  RAISE SQLSTATE '22012';
END;
$g$;

-- ERROR:  22012
-- CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE
1
2
3
4
5
6
7
8

5

do $g$
DECLARE
  user_id int := 100;
BEGIN
  RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
END;
$g$;

-- ERROR:  Duplicate user ID: 100
-- CONTEXT:  PL/pgSQL function inline_code_block line 5 at RAISE
1
2
3
4
5
6
7
8
9
10

2 捕获处理异常

捕获&&处理异常

语法

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

在EXCEPTION内的特殊变量：
- SQLSTATE
- SQLERRM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• condition：异常名字，有两种使用方式，所有异常列表在PG文档中可以找到《Appendix A. PostgreSQL Error Codes》，下面列举一部分。
  • 异常名：WHEN division_by_zero THEN，规律：小写下划线连接关键字
  • 异常码：WHEN SQLSTATE '22012' THEN ...，规律：5位字符，数字和任意字母组成
  • OTHERS：匹配一些异常
• handler_statements：异常处理语法块，如果这里面再产生异常不会被当前的EXCEPTION捕获，会直接抛到上层。
• 特殊变量：SQLSTATE、SQLERRM只在EXCEPTION语法块中生效，可以打印错误码和错误信息。
  • 例如：division_by_zero异常处理时，sqlerrm="division by zero"，sqlstate=22012

部分condition实例

Class 00 — Successful Completion
  00000	successful_completion

Class 01 — Warning
  01000	warning
  0100C	dynamic_result_sets_returned
  01008	implicit_zero_bit_padding
  01003	null_value_eliminated_in_set_function
  01007	privilege_not_granted
  01006	privilege_not_revoked
  01004	string_data_right_truncation
  01P01	deprecated_feature

Class 22 — Data Exception
  22000	data_exception
  2202E	array_subscript_error
  22021	character_not_in_repertoire
  22008	datetime_field_overflow
  22012	division_by_zero
  22005	error_in_assignment
  2200B	escape_character_conflict
  22022	indicator_overflow
  22015	interval_field_overflow
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

异常捕获实例

1 系统异常

do $g$
DECLARE
  user_id int := 100;
BEGIN
  user_id = user_id / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
END;
$g$;

-- NOTICE:  caught division_by_zero

do $g$
DECLARE
  user_id int := 100;
BEGIN
  user_id = user_id / 0;
EXCEPTION
    WHEN SQLSTATE '22012' THEN
        RAISE NOTICE 'caught division_by_zero';
END;
$g$;

-- NOTICE:  caught division_by_zero
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2 主动产生异常：没给错误码使用SQLSTATE P0001

没显示指定错误码，使用P0001、raise_exception：

do $g$
DECLARE
  user_id int := 100;
  text_var1 text;
  text_var2 text;
  text_var3 text;
  text_var4 text;
  text_var5 text;
BEGIN
  RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
EXCEPTION 
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                            text_var2 = PG_EXCEPTION_DETAIL,
                            text_var3 = PG_EXCEPTION_HINT,
                            text_var4 = PG_EXCEPTION_CONTEXT,
                            text_var5 = RETURNED_SQLSTATE;
    raise notice '%', text_var1;
    raise notice '%', text_var2;
    raise notice '%', text_var3;
    raise notice '%', text_var4;
    raise notice '%', text_var5;
END;
$g$;

-- NOTICE:  Nonexistent ID --> 100
-- NOTICE:  
-- NOTICE:  Please check your user ID
-- NOTICE:  PL/pgSQL function inline_code_block line 10 at RAISE
-- NOTICE:  P0001
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3 主动抛出异常：给定错误码

do $g$
DECLARE
  user_id int := 100;
  text_var1 text;
  text_var2 text;
  text_var3 text;
  text_var4 text;
  text_var5 text;
BEGIN
  RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
EXCEPTION 
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                            text_var2 = PG_EXCEPTION_DETAIL,
                            text_var3 = PG_EXCEPTION_HINT,
                            text_var4 = PG_EXCEPTION_CONTEXT,
                            text_var5 = RETURNED_SQLSTATE;
    raise notice '%', text_var1;
    raise notice '%', text_var2;
    raise notice '%', text_var3;
    raise notice '%', text_var4;
    raise notice '%', text_var5;
END;
$g$;

-- NOTICE:  Duplicate user ID: 100
-- NOTICE:  
-- NOTICE:  
-- NOTICE:  PL/pgSQL function inline_code_block line 10 at RAISE
-- NOTICE:  23505
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

4 特殊变量：注意sqlerrm会被异常文本替换掉，sqlstate永远是预定义好的错误码

do $g$
DECLARE
  user_id int := 100;
BEGIN
  RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
EXCEPTION 
  WHEN OTHERS THEN
    raise notice 'sqlstate: %', sqlstate;
    raise notice 'sqlerrm: %', sqlerrm;
END;
$g$;

-- NOTICE:  sqlstate: P0001
-- NOTICE:  sqlerrm: Nonexistent ID --> 100
1
2
3
4
5
6
7
8
9
10
11
12
13
14