PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获... - GET [STACKED] DIAGNOSTICS-阿里云开发者社区

开发者社区> 德哥> 正文

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

简介: 标签 PostgreSQL , GET , STACKED , DIAGNOSTICS 背景 使用GET STACKED DIAGNOSTICS捕获异常时的STACK内容。 使用GET 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;  
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》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
用SQL语句查找包含有某个关键字的存储过程、触发器、函数等(仅适用MS SQL SERVER)
原文:用SQL语句查找包含有某个关键字的存储过程、触发器、函数等(仅适用MS SQL SERVER) 第一种方法:利用系统表进行查询   --将text替换成你要查找的内容  select name  from sysobjects o, syscomments s  where o.id = s.id  and text like '%text%'  and o.xtype = 'P'    xtype:对象类型。
681 0
C#跨窗体相互调用对方函数
对象对象程序设计,为跨窗体调用提供了良药:即构造函数。只要将某一个窗体对象以参数形式传入到另一个窗体对象的构造函数,即可实现。 一、实际案例需求 图1 Form1窗体设计   图2 Form2窗体设计   如图1、图2,现在要求图1按下按键,自己锁住,弹出图2;然后图2按下按键,自己关闭,解放图1的按键。
853 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载