elimesika
asked on
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.
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$
good detail on postgres sql error codes: http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html
ASKER
Thanks
That's not answering my question.
I have asked how to get this info at runtime when an error occurs during SP execution.
That's not answering my question.
I have asked how to get this info at runtime when an error occurs during SP execution.
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.
>>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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot !
My pleasure. Glad to have helped. :-)