Solved

Error Handling

Posted on 2011-02-22
6
904 Views
Last Modified: 2012-06-27
HI
I am porting a database from MS SQL Server to Postgres.
One of the problems I have is how to get error information resulted from running a stored procedure.
The attached code does not run in Postgres , I need equivalents to the following MS SQL Server functions:
 
ERROR_NUMBER()
ERROR_SEVERITY();
ERROR_STATE();
ERROR_LINE();
ERROR_PROCEDURE()

Thanks for your help.

CREATE OR REPLACE FUNCTION rethrowerror()
  RETURNS void AS
$BODY$
   DECLARE
   v_ErrorMessage  VARCHAR(4000);
   v_ErrorNumber  TEXT;
   v_ErrorSeverity  INTEGER;
   v_ErrorState  INTEGER;
   v_ErrorLine  INTEGER;
   v_ErrorProcedure  VARCHAR(200);
BEGIN
   IF 'Error' /*NOT SUPPORTED ERROR_NUMBER()*/IS NULL then
      RETURN;
   end if;
     
    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

   v_ErrorNumber := 'Error' /*NOT SUPPORTED ERROR_NUMBER()*/;
   v_ErrorSeverity := ERROR_SEVERITY();
   v_ErrorState := ERROR_STATE();
   v_ErrorLine := ERROR_LINE();
   v_ErrorProcedure := coalesce(ERROR_PROCEDURE(),'-'); 

    /* Building the message string that will contain original
       error information. */

   v_ErrorMessage := N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' ||
   'Message: ' || 'Error'; 
   RAISE EXCEPTION '% % % % % % ',v_ErrorMessage,v_ErrorNumber,v_ErrorSeverity,v_ErrorState,v_ErrorProcedure,
   v_ErrorLine;
END; $BODY$

Open in new window

0
Comment
Question by:elimesika
  • 4
  • 2
6 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34958102
good detail on postgres sql error codes:  http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html
0
 
LVL 19

Author Comment

by:elimesika
ID: 34958781
Thanks
That's not answering my question.
I have asked how to get this info at runtime when an error occurs during SP execution.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34961622
I'm sorry, I was responding to this:

>>I need equivalents to the following MS SQL Server functions:
ERROR_NUMBER()
ERROR_SEVERITY();
ERROR_STATE();
ERROR_LINE();
ERROR_PROCEDURE()

Let me see if I can provide something more.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 34965277
Ok, I don't know if it is as clear as the SQL native error handling, but trapping the errors can be done with a BEGIN block, and and EXCEPTION.  It is described well in this link:  
http://www.network-theory.co.uk/docs/postgresql/vol2/TrappingErrors.html

The error conditions, howerver, is where it seems to get a little ugly.  See here:

>>>>
But if an error occurs within the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error that occurred. If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END. If no match is found, the error propagates out as though the EXCEPTION clause were not there at all: the error can be caught by an enclosing block with EXCEPTION, or if there is none it aborts processing of the function.
>>>>

This is a listing of the complete PostgreSQL Error Codes.  From what I've read, the first two digits of the error number indicate the error class, while the last three digits indicate the condition (or cause).
http://www.network-theory.co.uk/docs/postgresql/vol1/PostgreSQLErrorCodes.html

Also, I did find the example below, which is a little closer to the details you're looking for.  See here:
http://www.mt-soft.com.ar/2007/12/21/a-primer-on-postgres-exception-handling-for-the-php-developer-part-2/
CREATE OR REPLACE FUNCTION f_ex1 (
    INOUT  my_query text
) AS
$body$
BEGIN
        EXECUTE my_query INTO my_query;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'an EXCEPTION is about to be raised';
            RAISE EXCEPTION 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE PLPGSQL;

Open in new window

0
 
LVL 19

Author Closing Comment

by:elimesika
ID: 35012700
Thanks a lot !
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35012739
My pleasure.  Glad to have helped.  :-)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question