Postgres PLpgsql messages and errors

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.