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)