postgresql 数据库 plpgsql 的错误信息

淡淡的烟草味﹌ 2022-09-12 06:43 146阅读 0赞

os: centos 7.6.1810  
db: postgresql 10

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core) 
    # 
    # yum list installed|grep -i postgre
    postgresql10.x86_64                10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-contrib.x86_64        10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-devel.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-docs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-libs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg10  
    postgresql10-plperl.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython.x86_64       10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython3.x86_64      10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-pltcl.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-server.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-tcl.x86_64            2.7.5-1.rhel7                       @pgdg10  
    postgresql10-test.x86_64           10.18-1PGDG.rhel7                   @pgdg10

# plpgsql #

DO $$
    DECLARE
    
    	lv_i int4;
    	
    	v_state   TEXT;
    	v_msg     TEXT;
    	v_detail  TEXT;
    	v_hint    TEXT;
    	v_context TEXT;
    	
    BEGIN
       
      lv_i:=1/0;
    
    EXCEPTION
    		when OTHERS then
    	  
    		get stacked diagnostics
            v_state   = returned_sqlstate,
            v_msg     = message_text,
            v_detail  = pg_exception_detail,
            v_hint    = pg_exception_hint,
            v_context = pg_exception_context;
    
        raise notice E'exception:
            state  : %
            message: %
            detail : %
            hint   : %
            context: %', v_state, v_msg, v_detail, v_hint, v_context;
    
        raise notice E'exception:SQLSTATE: % SQLERRM: %', SQLSTATE, SQLERRM; 
    		
    END;
    
    $$ LANGUAGE plpgsql;

输出如下

> NOTICE:  exception:
            state  : 22012
            message: division by zero
            detail : 
            hint   : 
            context: SQL statement "SELECT 1/0"
    PL/pgSQL function inline_code_block line 14 at assignment
    > NOTICE:  exception:SQLSTATE: 22012 SQLERRM: division by zero
    > OK
    > 时间: 0.004s

参考:  
http://postgres.cn/docs/10/errcodes-appendix.html