This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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:
Prints a message and sends it to the client application and the postmaster logs.
RAISE NOTICE ''This is a notice.'';
Prints a message and only sends it to the postmaster logs.
RAISE DEBUG ''This is a debug.'';
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.
 
Continue to: