RAISE NOTICE

teledb=# CREATE OR REPLACE FUNCTION f28() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_int INTEGER := 1;
teledb$# BEGIN         
teledb$#     RAISE NOTICE 'v_int = %, 随机数 = %',v_int,random();
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f28();
NOTICE:  v_int = 1, 随机数 = 0.236714988015592
 f28 
-----

(1 row)

使用raise notice向终端输出一个消息，也有可能写到日志中(需要调整日志的保存级别)。

RAISE EXCEPTION

teledb=# CREATE OR REPLACE FUNCTION f28() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_int INTEGER := 1;
teledb$# BEGIN         
teledb$#     RAISE EXCEPTION '程序EXCEPTION ';
teledb$#     --下面的语句不会再执行
teledb$#     RAISE NOTICE 'v_int = %, 随机数 = %',v_int,random(); 
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f28();
ERROR:  程序EXCEPTION 

如果在事务中执行这个函数，则事务会终止(abort)。

RAISE EXCEPTION自定义ERRCODE

teledb=# CREATE OR REPLACE FUNCTION f28() RETURNS VOID AS
teledb-# $$  
teledb$# DECLARE
teledb$#     v_int INTEGER := 1;
teledb$# BEGIN         
teledb$#     RAISE EXCEPTION ' 程序EXCEPTION ' USING ERRCODE = '23505';
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f28();
ERROR:   程序EXCEPTION 

日志中会记录这个ERRCODE。

2023-08-24 14:54:58.909 CST,"teledb","teledb",335778,coord(335778,179),"192.168.30.5:48140",64e6fad7.51fa2,coord(335778,179),17,"SELECT",2023-08-24 14:38:15 CST,16/179,0,ERROR,23505," 程序EXCEPTION ",,,,,"PL/pgSQL function f28() line 5 at RAISE","SELECT f28();",,,"telesql"
2023-08-24 14:55:26.626 CST,"teledb","teledb",335778,coord(335778,180),"192.168.30.5:48140",64e6fad7.51fa2,coord(335778,180),18,"idle",2023-08-24 14:38:15 CST,16/180,0,ERROR,42601,"syntax error at or near ""exit""",,,,,,"exit