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:

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:

The results should resemble the following:

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.

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:

This should produce the following results:

Here it is important to note that the number of the placeholder and argument must be equal to avoid the following potential error:

The result should resemble this:

Following is another example that will create a possible error:

This will throw an error that resembles the following:

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.