The RAISE statement revisited

As shown in previous sections, RAISE can be used to either display a message or to throw an exception. Effectively, the aim of RAISE is to report errors. The fact that it can be used to display messages is just a consequence of reporting errors.

The first important thing to note is that the level at which an error is reported, such as INFO, is shown in the console or within the server logs, depending on the configuration of the show_client_min_messages and log_min_messages variables respectively. When used to report flow messages, RAISE should be used at a DEBUG level, which, by default, is not shown on the client console. Lowering the message level of client_min_messages, as shown in Listing 29, makes the message appear:

testdb=> SET client_min_messages TO debug;
testdb=> DO $code$
 BEGIN
   RAISE DEBUG 'A debug message';
   RAISE INFO  'An info message';
 END $code$;

DEBUG:  A debug message
INFO:  An info message

testdb=> SET client_min_messages TO info;
testdb=> DO $code$
 BEGIN
   RAISE DEBUG 'A debug message';
   RAISE INFO  'An info message';
 END $code$;

INFO:  An info message

Since RAISE can log messages to the server log, depending on the log_min_messages variable setting, it is better not to use RAISE to print out sensitive data.

The RAISE statement also allows for a set of extended attributes that are specified after the USING clause:

• HINT, which is a suggestion about the reported error.
• DETAIL, which is a detail about the reported error.

• ERRCODE, which is the name or the SQL State numeric value of a specific error.
• MESSAGE, which is usually used when reporting an exception. It is mutually exclusive with the message specified before the USING clause.

As an example, imagine a block of code that tries to perform the insertion of an already existing f_hash in the files table. The exception thrown from such a block of code could have been prepared as follows:

testdb=> DO $code$
 BEGIN
   -- do stuff
   ...
   RAISE USING
         ERRCODE = 'unique_violation',
         HINT = 'You should check your unique constraints',
         DETAIL = 'Cannot insert duplicated hash values!',
         MESSAGE = 'Cannot proceed further';
 END $code$;

ERROR:  Cannot proceed further
DETAIL:  Cannot insert duplicated hash values!
HINT:  You should check your unique constraints