SHOW WARNINGS/ERRORS

SHOW WARNINGS/ERRORS

功能描述

显示有关存储函数的信息。

注意事项

添加的系统参数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)，转换成一个十进制数字，即就是错误码的数字。不同的错误码数字对应不同的状态宏。

语法格式

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

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

参数说明

row_count

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

从第几行信息开始显示。 - 添加系统参数

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

返回结果集

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

示例

1. openGauss=# show sql_note;
2. sql_note
3. ----------
4. on
5. (1 row)

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

18. openGauss=# show count(*) warnings;
19. count
20. -------
21. 1
22. (1 row)

24. openGauss=# CREATE OR REPLACE FUNCTION TEST_FUNC(tempdata char) RETURNS VOID AS $$
25. openGauss$# BEGIN
26. openGauss$# raise info'TEST CHAR VALUE IS %',tempdata;
27. openGauss$# END;
28. openGauss$# $$ LANGUAGE plpgsql;
29. CREATE FUNCTION
30. openGauss=# select TEST_FUNC('abc'::clob);
31. INFO:  TEST CHAR VALUE IS abc
32. CONTEXT:  referenced column: test_func
33. test_func
34. -----------

36. (1 row)

38. openGauss=# show warnings;
39. level | code |        message
40. -------+------+------------------------
41. Note  |    0 | TEST CHAR VALUE IS abc
42. (1 row)

44. openGauss=# set sql_note=false;
45. SET
46. openGauss=# select TEST_FUNC('abc'::clob);
47. INFO:  TEST CHAR VALUE IS abc
48. CONTEXT:  referenced column: test_func
49. test_func
50. -----------

52. (1 row)

54. openGauss=# show warnings;
55. level | code | message
56. -------+------+---------
57. (0 rows)

59. openGauss=# SELECT pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
60. WARNING:  you don't own a lock of type ExclusiveLock
61. CONTEXT:  referenced column: pg_advisory_unlock
62. WARNING:  you don't own a lock of type ShareLock
63. CONTEXT:  referenced column: pg_advisory_unlock_shared
64. WARNING:  you don't own a lock of type ExclusiveLock
65. CONTEXT:  referenced column: pg_advisory_unlock
66. WARNING:  you don't own a lock of type ShareLock
67. CONTEXT:  referenced column: pg_advisory_unlock_shared
68. pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared
69. --------------------+---------------------------+--------------------+---------------------------
70. f                  | f                         | f                  | f
71. (1 row)

73. openGauss=# show warnings;
74. level  | code |                  message
75. ---------+------+--------------------------------------------
76. Warning |   64 | you don't own a lock of type ExclusiveLock
77. Warning |   64 | you don't own a lock of type ShareLock
78. Warning |   64 | you don't own a lock of type ExclusiveLock
79. Warning |   64 | you don't own a lock of type ShareLock
80. (4 rows)

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

(3 rows)

2. ```sql
3. --用sql_note控制存储note信息的开关。
4. CREATE OR REPLACE FUNCTION TEST_FUNC(tempdata char) RETURNS VOID AS $$
5. BEGIN
6. raise info'TEST CHAR VALUE IS %',tempdata;
7. END;
8. $$ LANGUAGE plpgsql;
9. select TEST_FUNC('abc'::clob);
10. INFO:  TEST CHAR VALUE IS abc
11. CONTEXT:  referenced column: test_func
12. test_func
13. -----------

15. (1 row)

17. show warnings;
18. level | code |        message
19. -------+------+------------------------
20. Note  |    0 | TEST CHAR VALUE IS abc
21. (1 row)

23. set sql_note=false;
24. select TEST_FUNC('abc'::clob);
25. INFO:  TEST CHAR VALUE IS abc
26. CONTEXT:  referenced column: test_func
27. test_func
28. -----------

30. (1 row)

32. show warnings;
33. level | code | message
34. -------+------+---------
35. (0 rows)