SHOW WARNINGS/ERRORS

Function

Displays the sql that was executed in the current session and the resulting messages (including errors, warnings, notes).

Precautions

The added system parameter, sql_note, is a switch that sets whether show warnings displays Note-level messages.
The Code field is the error code for the message. Its numeric meaning corresponds to the macro definition in ERRCODE. The status macros of various messages are generated by MAKE_SQLSTATE(ch1, ch2, ch3, ch4, ch5). MAKE_SQLSTATE works by subtracting '0' from the ascii code of the characters ch1 ~ ch5, and then taking the last six bits of their binary to get res1 ~ res5, and then composing the five data from low to high to form a 30-bit binary result (res1 ~ res5), and then composing these five data from low to high to form a 30-bit binary result (res1 ~ res5), which is a 30-bit binary result. bit binary result (res5res4res3res2res1), which is converted to a decimal number, i.e. the number of the error code. Different error code numbers correspond to different status macros.

Syntax

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

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

Parameter Description

row_count

Outputs the last sql, with a limit on the number of rows of warnings/errors messages generated.
offset

Starts displaying from the first line of message.
add system parameters

sql_note This parameter is a switch that sets whether show warnings displays Note level messages or not.

Return Result Set

Field Name	Type	Description
Level	character type	Level of message (Note/Warning/Error)
Code	integer type	Error code corresponding to the message status
Message	character type	Message content

Examples

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

MogDB=# create table test(id int, name varchar default 11);
CREATE TABLE
MogDB=# 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
MogDB=# show warnings limit 1;
 level |   code    |                      message
-------+-----------+---------------------------------------------------
 Error | 117571716 | relation "test" already exists in schema "public"
(1 row)

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

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

(1 row)

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

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

(1 row)

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

MogDB=# 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)

MogDB=# 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)

MogDB=# 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)

-- Use sql_note to control the switch for storing note information.
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)