Postgres PLpgsql messages and errors
Introduction
This tutorial will provide explanations and examples for working with Postgres PLpgsql messages and errors. Errors can be raised in Postgres via the EXCEPTION
level in the RAISE statement and will both display an error message and stop the execution of the function. As the term implies, RAISE statements will raise errors during a PLpgsql function’s operation. More information about an error can be added with the USING option
and Postgres provides various USING option phrases.
Prerequisites
- PostgreSQL must be properly installed and configured on the local operating system. The latest version of PostgreSQL can be download here.
Postgres PLpgsl Error Reporting
Errors can be raised in Postgres via the EXCEPTION
level in the RAISE statement. This is the default level used by Postgres. More information about the error can be added with the USING option
phrase.
Following is the basic form of the USING option syntax:
1 | USING option = -expression- |
Following are the various USING options Postgres provides:
- SQLSTATE – This option provides a unique code that defines a particular error.
- ERRCODE – This error code can be a five-character code or a condition name.
- DETAIL – This option provides a piece of detailed information against the generated error.
- MESSAGE – Provides a plain text message of the error.
- HINT – This provides a “hint” that makes for easier error detection and discovery.
The following example shows how to determine if a specific phone number currently exists in a database:
1 2 3 4 5 6 7 8 9 10 | DO $$ DECLARE phone varchar(255) := '+(63)028-320-7029'; BEGIN -- check phone if already exist in a database -- ... -- report duplicate phone RAISE EXCEPTION 'Phone already exist: %', phone USING HINT = 'Provide another phone number'; END $$; |
The results should resemble the following:
1 2 | [Err] ERROR: Phone already exist: +(63)028-320-7029 HINT: Provide another phone number |
Postgres PLpgsql Message Reporting
This section will explain how to use the RAISE statement to raise an error.
Following is the basic form of the RAISE statement syntax.
1 | RAISE <level> format |
As shown in the above example, the RAISE statement is followed by the ‘level’ parameter that indicates the severity of the error.
The levels that will be used in this tutorial are:
- LOG
- NOTICE
- DEBUG
- INFO
- EXCEPTION
- WARNING
It should be noted that not specifying the level will result in the default EXCEPTION level being used by the RAISE statement. This will raise an error and stop the current process or transaction.
The format is a simple string literal, characters from the source character set enclosed in double quotation marks (” “), that describes the reported error. Here the format utilizes the percentage (%) sign that will be replaced by the succeeding optional argument’s value.
The below example shows how to use the RAISE statement to provide different reports at a specified time. Here the current time is used in this example by using the now()
method as the optional argument:
1 2 3 4 5 6 7 8 | DO $$ BEGIN RAISE LOG 'from LOG level message %', now(); RAISE INFO 'from INFO level message %', now() ; RAISE DEBUG 'from DEBUG level message %', now(); RAISE NOTICE 'from NOTICE level message %', now(); RAISE WARNING 'from WARNING level message %', now(); END $$; |
This should produce the following results:
1 2 3 | INFO: from INFO level message 2020-03-04 11:44:31.230376+08 NOTICE: from NOTICE level message 2020-03-04 11:44:31.230376+08 WARNING: from WARNING level message 2020-03-04 11:44:31.230376+08 |
Here it is important to note that the number of the placeholder and argument must be equal to avoid the following potential error:
1 2 3 4 | DO $$ BEGIN RAISE LOG 'from LOG level message %, %', now(); END $$; |
The result should resemble this:
1 | ERROR: too few parameters specified for RAISE |
Following is another example that will create a possible error:
1 2 3 4 | DO $$ BEGIN RAISE LOG 'from LOG level message %', now(), date(); END $$; |
This will throw an error that resembles the following:
1 | ERROR: too many parameters specified for RAISE |
Conclusion
This tutorial provided explanations and examples of working with Postgres PLpgsql messages and errors. The tutorial covered the basics of Postgres PLpgsl error reporting, how errors can be raised in Postgres via the EXCEPTION
level in the RAISE statement and how information about the error can be added with the USING option
phrase. The article then gave the basic form of the USING option with examples of Postgres PLpgsql message reporting and provided a list of the levels used in this tutorial. Remember that not specifying the level will result in the default EXCEPTION level being used by the RAISE statement, raising errors and aborting the process.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started