How to raise errors and report messages within stored procedures and functions

December 13, 2019

SUMMARY: This article discusses the RAISE command for reporting errors, warnings, and other report messages within stored procedures and functions in PostgreSQL. Levels of error messages are covered along with settings for specifying their display to the client or log.

                    1. Syntax


                   2. RAISE INFO/WARNING/NOTICE


                   3. RAISE LOG/DEBUG


                   4. RAISE EXCEPTION


                   5. USING option = expression

 

PL/pgSQL is one of the most popular procedural languages in PostgreSQL. It provides the capability of creating functions and procedures that help the user perform reusable complex computations. A typical procedure is created using different procedural constructs including block structures, variables, SQL commands, and error-handling. 

In PostgreSQL, RAISE is used to report errors and messages. In this article, we will be focusing on how to use RAISE to implement error-handling within stored procedures and functions.

RAISE is used to raise errors and report messages, PostgreSQL provides various parameters to report an error, warning, and information at a detailed level. Below is the basic syntax for the RAISE command.

 

Syntax

RAISE [level] [format]

 

Level indicates error severity. The level can be (in order from least to most severe) DEBUG, LOG, INFO, NOTICE, WARNING, or EXCEPTION. EXCEPTION is the default level and the only one that will halt the procedure. Each level generates an error message with detailed information based on priority levels.

Users can control where these error messages will be reported (i.e., on client screen, server logs or on both) by setting the postgresql.conf parameters “log_min_messages” and “client_min_messages.”

Format specifies the error message that the user wants to display. If the message needs some variable values to be printed with it, the “%” sign is used. The “%” sign acts as a placeholder and is replaced by the variable value given with the RAISE command. This can be used multiple times. 

Let’s demonstrate all of this with an example:

postgres=# CREATE OR REPLACE PROCEDURE example1 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE NOTICE 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example1 ();

NOTICE:  value of a : 10

CALL

 

In this example “RAISE NOTICE 'value of a : %', a; is used to print “NOTICE:  value of a : 10” when the procedure is called. The severity level of NOTICE is used to print the message “value of a” and “%” is replaced by the value of variable “a,” which is 10 in this instance.

 

RAISE INFO/WARNING/NOTICE

These are generally reported back to the client based on “client_min_messages” and “log_min_messages” settings. INFO, WARNING, and NOTICE can be used as named states to display warning information to the user.

postgres=# CREATE OR REPLACE PROCEDURE example2 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE NOTICE 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE WARNING 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : % at %: ', a, now(); 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example2 ();

NOTICE:  value of a : 10 at 2019-11-24 18:26:34.919079+05:30: 

WARNING:  value of a : 20 at 2019-11-24 18:26:34.919079+05:30: 

INFO:  value of a : 30 at 2019-11-24 18:26:34.919079+05:30: 

CALL

 

RAISE LOG/DEBUG

The LOG and DEBUG levels are generally not reported back to the client under the default “client_min_messages” and “log_min_messages” settings. 

postgres=# CREATE OR REPLACE PROCEDURE example3 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE LOG 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE DEBUG 'value of a : % at %: ', a, now(); 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example3 ();

CALL

 

As seen above the procedure is called and nothing is printed to the client. By default, LOG will output a message in the server log. 

[edb@localhost bin]$ tail logfile 

2019-11-24 18:31:15.611 IST [85200] LOG:  value of a : 10 at 2019-11-24 18:31:15.610122+05:30: 

2019-11-24 18:31:15.611 IST [85200] CONTEXT:  PL/pgSQL function example3() line 5 at RAISE

2019-11-24 18:31:15.611 IST [85200] STATEMENT:  CALL example3 ();

 

DEBUG will output to the client when “client_min_messages” is set to the DEBUG level.

postgres=# set client_min_messages = debug;

SET

postgres=# CALL example3 ();

LOG:  value of a : 10 at 2019-11-24 18:34:17.902974+05:30: 

DEBUG:  value of a : 20 at 2019-11-24 18:34:17.902974+05:30: 

CALL

 

RAISE EXCEPTION

When no level is specified, EXCEPTION is used by default. EXCEPTION will abort the current transaction.

postgres=# CREATE OR REPLACE PROCEDURE example4 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE 'value of a : %', a; 

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example4 ();

ERROR:  value of a : 10

CONTEXT:  PL/pgSQL function example4() line 5 at RAISE

 

As seen above, when the RAISE statement does not specify a level, the message is printed as an ERROR, and the transaction is aborted, so the next RAISE statement isn't executed. The same output will be produced when “RAISE EXCEPTION 'value of a : %', a;” is used instead.

RAISE can be used with various options to make the error message more readable and informative by using below syntax.

 

USING option = expression

Options can be MESSAGE, DETAIL, HINT, ERRCODE, etc., and expression is a single value statement.

postgres=# CREATE OR REPLACE PROCEDURE example5 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE EXCEPTION 'value of a : %', a USING HINT = 'EXCEPTION is raised here and transaction aborted';

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example5 ();

ERROR:  value of a : 10

HINT:  EXCEPTION is raised here and transaction aborted

CONTEXT:  PL/pgSQL function example5() line 5 at RAISE

 

Along with the ERROR message this example also outputs a HINT property back to the client. In a similar way, MESSAGE, DETAIL, and ERRCODE can also be used.

 

Reference Links

https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html

http://www.postgresqltutorial.com/plpgsql-errors-messages/