SHOW WARNINGS/ERRORS
Function
SHOW WARNINGS/ERRORS displays information about storage functions.
Precautions
The added system parameter sql_note determines whether to display note-level information in SHOW WARNINGS.
The Code column shows the error codes. The meaning of the number corresponds to the macro definition in ERRCODE. The status macros of various information are generated by the MAKE_SQLSTATE(ch1, ch2, ch3, ch4, ch5). MAKE_SQLSTATE is used to subtract '0' from the ASCII codes of ch1 to ch5, and then obtain the last six bits of the binary codes to obtain res1 to res5. The five data records form a 30-bit binary result (res5res4res3res2res1) from the least significant bit to the most significant bit. The result is converted into a decimal number, that is, 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
Maximum rows of warnings/errors information generated by the previous SQL statement.
offset
Sequence number of the row from which the information is displayed.
Added system parameter
sql_note determines whether to display note-level information in SHOW WARNINGS.
Return Result Set
| Column | Type | Description |
|---|---|---|
| Level | Character | Information level (Note/Warning/Error) |
| Code | Integer | Error code corresponding to the information status |
| Message | Character | Information content |
Examples
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)
(3 rows)
```sql
--sql\_note is used to determine whether to store 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)