Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error Handling

Posted on 2011-02-22
6
Medium Priority
?
920 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

704 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