PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获... - GET [STACKED] DIAGNOSTICS

标签

PostgreSQL , GET , STACKED , DIAGNOSTICS

背景

使用GET STACKED DIAGNOSTICS捕获异常时的STACK内容。

使用GET DIAGNOSTICS捕获运行过程中的状态值。

GET DIAGNOSTICS捕获运行过程中的状态值

There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];  

GET DIAGNOSTICS integer_var = ROW_COUNT;  

The GET DIAGNOSTICS command, previously described in Section 43.5.5, retrieves information about current execution state (whereas the GET STACKED DIAGNOSTICS command discussed above reports information about the execution state as of a previous error).

例子

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$  
BEGIN  
  RETURN inner_func();  
END;  
$$ LANGUAGE plpgsql;  
  
CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$  
DECLARE  
  stack text;  
BEGIN  
  GET DIAGNOSTICS stack = PG_CONTEXT;  
  RAISE NOTICE E'--- Call Stack ---\n%', stack;  
  RETURN 1;  
END;  
$$ LANGUAGE plpgsql;  
  
SELECT outer_func();  
  
NOTICE:  --- Call Stack ---  
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS  
PL/pgSQL function outer_func() line 3 at RETURN  
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN  
 outer_func  
 ------------  
           1  
(1 row)  

GET STACKED DIAGNOSTICS捕获异常时的STACK内容

GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT returns the same sort of stack trace, but describing the location at which an error was detected, rather than the current location.

例子

DECLARE  
  text_var1 text;  
  text_var2 text;  
  text_var3 text;  
BEGIN  
  -- some processing which might cause an exception  
  ...  
EXCEPTION WHEN OTHERS THEN  
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,  
                          text_var2 = PG_EXCEPTION_DETAIL,  
                          text_var3 = PG_EXCEPTION_HINT;  
END;  

参考

https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACK

https://www.postgresql.org/docs/11/static/plpgsql-statements.html

《Using "GET DIAGNOSTICS integer_var = ROW_COUNT;" capture rows effect by the last SQL》