Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Handling Errors and Exceptions

RAISE statements raise errors and exceptions during a PL/pgSQL function's operation. A RAISE statement sends specified information to the PostgreSQL elog mechanism (the standard PostgreSQL error logging utility, which typically logs data either to /var/log/messages , or to $PGDATA/serverlog , as well as displaying to stderr ).

A RAISE statement is also given the level of error it should raise, and the string it should send to PostgreSQL. Additionally, you can list variables and expressions whose values you wish to have placed into the string. Use percent signs (%) to mark the locations in the string at which you want those values inserted. The syntax of the RAISE statement is as follows:

RAISE 
level
 ''
message string
'' [, 
identifier
 [...] ];

Table 11-1 lists the three possible values for the RAISE statement's level and their meanings.

Table 11-1. Possible level values

Value Explanation

DEBUG

DEBUG level statements send the specified text as a DEBUG: message to the PostgreSQL log and the client program if the client is connected to a database cluster running in debug mode. DEBUG level RAISE statements will be ignored by a database running in production mode.

NOTICE

NOTICE level statements send the specified text as a NOTICE: message to the PostgreSQL log and the client program in any PostgreSQL operation mode.

EXCEPTION

EXCEPTION level statements send the specified text as an ERROR: message to the client program and the PostgreSQL database log. The EXCEPTION level also causes the current transaction to be aborted.

In Example 11-48, the first RAISE statement raises a debug level message. The second and third RAISE statements send a notice to the user. Notice the use of the percent-sign (%) in the third RAISE statement to mark the location in the string at which the value of an integer is to be inserted. Finally, the fourth RAISE statement displays an error and throws an exception, causing the function to end and the transaction to be aborted.

Example 11-48. Using the RAISE statement

CREATE FUNCTION raise_test () RETURNS integer AS '
  DECLARE
     
     -- Declare an integer variable for testing.
    an_integer INTEGER = 1;
  
  BEGIN
     
     -- Raise a debug level message.
    RAISE DEBUG ''The raise_test() function began.'';
    
    an_integer = an_integer + 1;
     
     -- Raise a notice stating that the an_integer variable was changed,
     -- then raise another notice stating its new value.
    RAISE NOTICE ''Variable an_integer was changed.'';
    RAISE NOTICE ''Variable an_integer's value is now %.'',an_integer;
     
     -- Raise an exception.
    RAISE EXCEPTION ''Variable % changed.  Transaction aborted.'',an_integer;
    
    RETURN 1;
  END;
' LANGUAGE 'plpgsql';

Example 11-49 shows the results of the raise_test() function when called from our booktown database. The DEBUG output does not show, because our database is not running in debug mode.

Example 11-49. Results of the raise_test( ) function

booktown=# 
SELECT raise_test();

NOTICE:  Variable an_integer was changed.
NOTICE:  Variable an_integer's value is now 2.
ERROR:  Variable 2 changed.  Aborting transaction.
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire