The RAISE command is provided by PL/pgSQL to give the user the ability to output messages to the PostgreSQL elog mechanism. The elog() function is used to send messages to the front-end. To use the RAISE command, follow this format:

   RAISE level 'format' [, identifier [...]];
   

There is a special % character commonly used as a placeholder for a variable. The variable is then listed after a comma and the % is placed at the desired location where the variable would be. For example, this RAISE EXCEPTION command will print the value held by the user_name variable:

   RAISE NOTICE ''The user % does not exist.'', user_name;
   

If the value of user_name is mark, then the message would say:

    NOTICE: The user mark does not exist.
    

	Using Quotes
	In PostgreSQL, double quotes have a special meaning, so if a function is being defined in PostgreSQL, then the function cannot use double quotes. To print a RAISE message, place the desired message inside a set of two single quotes. One single quote escapes the other quote, which is the actual single quote required to use the RAISE command.

There are different types of levels to print a message. They can be any of:

NOTICE

Prints a message and sends it to the client application and the postmaster logs.

       RAISE NOTICE ''This is a notice.'';
       

DEBUG

Prints a message and only sends it to the postmaster logs.

      RAISE DEBUG ''This is a debug.'';
       

EXCEPTION

Prints a message to the postmaster logs and terminates the current query or transaction.

      RAISE EXCEPTION ''This is an exception.'';
       

The following function illustrates the different levels of handling error messages. If a user supplies a number 10 or below, it will use the notice level to print out a message. Anything from 11 to 20 uses the debug mechanism to print a message to the logs. It also prints a notice message because the debug mechanism will not print to the client application. Lastly, numbers greater than 20 use the exception level to terminate the transaction and print a message to the client application.

Example 9-17. PL/pgSQL RAISE Message levels

CREATE FUNCTION raise_msg(integer) RETURNS integer AS'
DECLARE
  -- defines an alias name for the input
  num ALIAS FOR $1;
  
BEGIN
  -- if the number supplied is 10 or less
  IF num <= 10 THEN
  -- prints a notice and displays the number
  RAISE NOTICE ''This notice displays the number %.'', num;

    -- if the number supplied is 20 or less
    ELSE IF num <= 20 THEN
    -- prints a debug message to the logs
    RAISE DEBUG ''This debug sends the number % to the logs.'', num; 
    -- also prints a notice 
    RAISE NOTICE ''The debug message has been sent.'';

    -- if the number supplied is 21 or greater
    ELSE 
    -- prints an exception message and displays the number
    RAISE EXCEPTION ''This exception prints % and ends the transaction.'', num; 
    -- end of else if body  
    END IF;

 -- end of if body
 END IF;

-- displays the number supplied
return num;
END;
' LANGUAGE 'plpgsql';
   

When a 5 was supplied to this function, it returns:

 
NOTICE:  This notice displays the number 5.
 raise_msg
-----------
         5
(1 row)
   

Then an 11 was passed to the function and it displayed:

 
NOTICE:  The debug message has been sent.
 raise_msg
-----------
        11
(1 row)
   

Notice, this message did not say,

    
      "

DEBUG: this debug sends the number 11 to the logs.

" 
       

It is because the DEBUG mechanism only prints to the logs. If you would like to view the DEBUG message, you can perform a tail command on the path /usr/local/pgsql/data/serverlog . Once executed, it should display a list of messages that were generated. The last message should be the DEBUG message:

 
DEBUG:  This debug sends the number 11 to the logs.
   

Lastly, a 55 was given as a parameter and the function answered with the message:

ERROR:  This exception prints 55 and ends the transaction.  
   

The result of the RAISE EXCEPTION command did not printe the number 55 in a column like the other messages. That is because the EXCEPTION level stops the transaction from finishing. Therefore, you only receive a message and it doesn't return the number 55 to the client application.