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.
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/