SHOW WARNINGS/ERRORS

功能描述

显示当前会话执行的sql,所产生的信息(包含errors, warnings, notes)。

注意事项

  • 添加的系统参数sql_note,是设置show warnings是否显示Note级别的信息开关。

  • Code字段是信息的错误码。其数字含义对应ERRCODE中的宏定义。其中各种信息的状态宏都是由 MAKE_SQLSTATE(ch1, ch2, ch3, ch4, ch5)生成。MAKE_SQLSTATE作用是把字符 ch1 ~ ch5 的ascii码减去'0',再取其二进制的后六位得到 res1 ~ res5,然后把这5个数据从低到高位组成一个30位二进制结果(res5res4res3res2res1),转换成一个十进制数字,即就是错误码的数字。 不同的错误码数字对应不同的状态宏。

语法格式

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

参数说明

  • row_count

    输出上条sql,生成的warnings/errors信息的行数限制。

  • offset

    从第几行信息开始显示。

  • 添加系统参数

    sql_note 该参数是设置show warnings是否显示Note级别的信息开关

返回结果集

字段名类型说明
Level字符类型信息的级别(Note/Warning/Error)
Code整数类型信息状态对应的错误码
Message字符类型信息内容

示例

openGauss=# show sql_note;
 sql_note
----------
 on
(1 row)

openGauss=# create table test(id int, name varchar default 11);
CREATE TABLE
openGauss=# create table test(id int, name varchar default 11);
ERROR:  relation "test" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
openGauss=# show warnings limit 1;
 level |   code    |                      message
-------+-----------+---------------------------------------------------
 Error | 117571716 | relation "test" already exists in schema "public"
(1 row)

openGauss=# show count(*) warnings;
 count
-------
     1
(1 row)

openGauss=# CREATE OR REPLACE FUNCTION TEST_FUNC(tempdata char) RETURNS VOID AS $$
openGauss$# BEGIN
openGauss$# raise info'TEST CHAR VALUE IS %',tempdata;
openGauss$# END;
openGauss$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
openGauss=# select TEST_FUNC('abc'::clob);
INFO:  TEST CHAR VALUE IS abc
CONTEXT:  referenced column: test_func
 test_func
-----------

(1 row)

openGauss=# show warnings;
 level | code |        message
-------+------+------------------------
 Note  |    0 | TEST CHAR VALUE IS abc
(1 row)

openGauss=# set sql_note=false;
SET
openGauss=# select TEST_FUNC('abc'::clob);
INFO:  TEST CHAR VALUE IS abc
CONTEXT:  referenced column: test_func
 test_func
-----------

(1 row)

openGauss=# show warnings;
 level | code | message
-------+------+---------
(0 rows)

openGauss=# SELECT pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
WARNING:  you don't own a lock of type ExclusiveLock
CONTEXT:  referenced column: pg_advisory_unlock
WARNING:  you don't own a lock of type ShareLock
CONTEXT:  referenced column: pg_advisory_unlock_shared
WARNING:  you don't own a lock of type ExclusiveLock
CONTEXT:  referenced column: pg_advisory_unlock
WARNING:  you don't own a lock of type ShareLock
CONTEXT:  referenced column: pg_advisory_unlock_shared
 pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared
--------------------+---------------------------+--------------------+---------------------------
 f                  | f                         | f                  | f
(1 row)

openGauss=# show warnings;
  level  | code |                  message
---------+------+--------------------------------------------
 Warning |   64 | you don't own a lock of type ExclusiveLock
 Warning |   64 | you don't own a lock of type ShareLock
 Warning |   64 | you don't own a lock of type ExclusiveLock
 Warning |   64 | you don't own a lock of type ShareLock
(4 rows)

openGauss=# show warnings limit 2, 4;
  level  | code |                  message
---------+------+--------------------------------------------
 Warning |   64 | you don't own a lock of type ExclusiveLock
 Warning |   64 | you don't own a lock of type ShareLock
(2 rows)

--用sql_note控制存储note信息的开关。
CREATE OR REPLACE FUNCTION TEST_FUNC(tempdata char) RETURNS VOID AS $$
BEGIN
	raise info'TEST CHAR VALUE IS %',tempdata;  
END;
$$ LANGUAGE plpgsql;
select TEST_FUNC('abc'::clob);
INFO:  TEST CHAR VALUE IS abc
CONTEXT:  referenced column: test_func
 test_func 
-----------
 
(1 row)

show warnings;
 level | code |        message         
-------+------+------------------------
 Note  |    0 | TEST CHAR VALUE IS abc
(1 row)

set sql_note=false;
select TEST_FUNC('abc'::clob);
INFO:  TEST CHAR VALUE IS abc
CONTEXT:  referenced column: test_func
 test_func 
-----------
 
(1 row)

show warnings;
 level | code | message 
-------+------+---------
(0 rows)
意见反馈
编组 3备份
    openGauss 2024-10-08 01:19:39
    取消