标签
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;
参考
《Using "GET DIAGNOSTICS integer_var = ROW_COUNT;" capture rows effect by the last SQL》
来源: https://yq.aliyun.com/articles/700347