Many people who have used Oracle in the past might be familiar with the GET DIAGNOSTICS clause. The idea behind the GET DIAGNOSTICS clause is to allow users to see what is going on in the system. While the syntax might appear a bit strange to people who are used to modern code, it is still a valuable tool that can make your applications better.

From my point of view, there are two main tasks that the GET DIAGNOSTICS clause can be used for:

• Inspecting the row count
• Fetching context information and getting a backtrace

Inspecting the row count is definitely something that you will need during everyday programming. Extracting context information will be useful if you want to debug applications.

The following example shows how the GET DIAGNOSTICS clause can be used inside your code:

CREATE FUNCTION get_diag() RETURNS int AS 
$$ 
DECLARE 
  rc  int; 
  _sqlstate text; 
  _message text; 
  _context text; 
BEGIN 
  EXECUTE 'SELECT * FROM generate_series(1, 10)'; 
  GET DIAGNOSTICS rc = ROW_COUNT; 
  RAISE NOTICE 'row count: %', rc; 
  SELECT rc / 0;  
EXCEPTION 
  WHEN OTHERS THEN 
    GET STACKED DIAGNOSTICS 
    _sqlstate = returned_sqlstate, 
    _message = message_text, 
    _context = pg_exception_context;  
    RAISE NOTICE 'sqlstate: %, message: %, context: [%]', 
                 _sqlstate, 
                 _message, 
                 replace( _context, E'n', ' <- ' ); 
  RETURN rc; 
END; 
$$ LANGUAGE 'plpgsql'; 

The first thing after declaring those variables is to execute a SQL statement and ask the GET DIAGNOSTICS clause for a row count, which is then displayed in a debug message. Then, the function forces PL/pgSQL to error out. Once this happens, we will use the GET DIAGNOSTICS clause to extract information from the server to display it.

Here is what happens when we call the get_diag function:

test=# SELECT get_diag();  
NOTICE:  row count: 10 
CONTEXT:   PL/pgSQL function get_diag() line  12 at RAISE  
NOTICE:  sqlstate: 22012, 
message: division by zero, 
context: [SQL  statement "SELECT rc / 0" 
<- PL/pgSQL function get_diag() line  14 at 
SQL statement] 
CONTEXT:  PL/pgSQL function get_diag() line  22 at RAISE
 get_diag 
---------- 
 10 
(1 row) 

As you can see, the GET DIAGNOSTICS clause gives us detailed information about the activities in the system.