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

作者

digoal

日期

2018-10-29

标签

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;

Name| Type| Description
---|---|---
ROW_COUNT| bigint| the number of rows processed by the most recent SQL command
RESULT_OID| oid |the OID of the last row inserted by the most recent SQL command (only useful after an INSERT command into a table having OIDs)
PG_CONTEXT| text| line(s) of text describing the current call stack (see Section 43.6.8)

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.

Name| Type| Description
---|---|---
RETURNED_SQLSTATE |text |the SQLSTATE error code of the exception
COLUMN_NAME |text |the name of the column related to exception
CONSTRAINT_NAME| text| the name of the constraint related to exception
PG_DATATYPE_NAME| text| the name of the data type related to exception
MESSAGE_TEXT |text |the text of the exception's primary message
TABLE_NAME| text| the name of the table related to exception
SCHEMA_NAME| text| the name of the schema related to exception
PG_EXCEPTION_DETAIL| text| the text of the exception's detail message, if any
PG_EXCEPTION_HINT| text| the text of the exception's hint message, if any
PG_EXCEPTION_CONTEXT| text |line(s) of text describing the call stack at the time of the exception (see Section 43.6.8)

例子

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》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.