[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

Error Handling

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
elimesika
Asked:
elimesika
  • 4
  • 2
1 Solution
 
dbaSQLCommented:
good detail on postgres sql error codes:  http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html
0
 
elimesikaAuthor Commented:
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
 
dbaSQLCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dbaSQLCommented:
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
 
elimesikaAuthor Commented:
Thanks a lot !
0
 
dbaSQLCommented:
My pleasure.  Glad to have helped.  :-)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now