PL/pgSQL Errors and Messages

One of the significant features of Postgres is “error handling”. Postgres offers several methods to display/show these errors to users and notify them through messages so that the program can be executed normally as expected and without any interruption.

In this article, we will learn about some of the built-in errors in PostgreSQL and how these errors are raised using the RAISE statement.

PL/pgSQL Errors and Messages

The errors and messages need to be raised in PostgreSQL in order to report them to the user. Reporting the errors to the user is important as it helps in understanding the reason behind the error so that it can be fixed.

Report Messages

To raise a message, we make use of the RAISE keyword. The basic syntax for raising a message is:

RAISE level format;

Let's have a look at the syntax of the above query and try to understand what is what. You can see that the RAISE statement is followed by the level option. Level basically depicts and shows the seriousness of the particular error. PostgreSQL offers several levels that are listed below:

● DEBUG

● INFO

● NOTICE

● LOG

● EXCEPTION

● WARNING

Note that if there is no level specified then by default, the RAISE statement considers it as an EXCEPTION level. In this case, it would stop the current transaction.

Next is the format. The format is basically a string/text that determines the message to be shown. The format contains a % placeholder, that will be replaced by the provided argument. It is a crucial point that the placeholders and number of arguments should be equal. Otherwise, this situation will throw an error.

Let’s see an example of how the RAISE statement works.

Example 1: Understanding Reporting Messages

In the example below, the RAISE statement reports multiple error messages at the same instance:

DO $$ 
BEGIN 
RAISE LOG 'Log   message is raised at: %', now() ;
RAISE DEBUG 'Debug message is raised at: %', now();
RAISE INFO 'Information message is raised at: %', now();
RAISE NOTICE 'Notice message is raised at: %', now();
RAISE WARNING 'Warning message is raised at: %', now();
END $$;

The now() function is used to get the current time and then that time is substituted by the % placeholder. The output for the above-written query is given as:

Here one thing to be noticed is that not every message is reported back to the client/user. Only messages of type INFO, NOTICE, and WARNING are reported back. These settings are however administered by client_min_messages and log_min_messages options.

Raising Errors

The syntax for raising an error is the same as for reporting messages. However, the level for raising errors is the EXCEPTION level. But if it is not written, the RAISE statement still considers it EXCEPTION by default. Besides level, you can also specify more information with the RAISE statement. You can do this by writing the following query:

USING option = expression

The option here can be of any type given below:

MESSAGE: Sets the text of the error message.

HINT: Provides us with hints(helps to discover the actual cause of error).

DETAIL: This gives us detailed/precise facts about the error.

ERRCODE: Identifies the error code.

Example 2: Raising Errors in PL/pgSQL

Let’s have a look at an example to cover this concept:

do $$ 
 declare
  Username varchar(255) := 'alexpeter';
 begin 
  --   check Username is available or not
  -- …
raise exception 'Username % not Available :(', Username
 using hint = 'Please   select some other username';
 end $$;

In the above example, the user checks for the username if it is already in use or not. The output in this case is:

Example 3: Raising SQLSTATE Error

Now let's consider another example, for that, we will write the query for SQLSTATE.

DO $$ 
 BEGIN 
  --...
  RAISE SQLSTATE '4510B';
 END $$;

The output for the code is:

We can see that the SQLSTATE error has been raised by the above query.

Example 4: Raising Errors in PL/pgSQL

Consider the following provided query to assess the more about raising error in PL/pgSQL:

DO $$ 
 BEGIN 
  --...
  RAISE invalid_regular_expression;
 END $$;

In this case, the error raised is “invalid_regular_expression”.

In this way, we can raise the errors to make the users aware of the errors raised.

Conclusion

PostgreSQL has some built-in errors. We must display these errors/issues to the user and report them via messages so that the program is executed normally without any interruption. In this article, we have learned about some of the built-in errors in PostgreSQL and how these errors are raised with practical examples.

PL/pgSQL Errors and Messages