Solved

Error Handling

Posted on 2011-02-22
6
912 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

751 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