I did some plpgsql functions. I have my own user table containing some custom errors. My intention is to throw this errors to the application. To do so, I must catch the original PostGreSQL error and, based oin this ID, to send the custom error with a raise or something. In SQL Server it was very simple becuase I get the @@error from sysmessages and, based on that value, I sent the custom error. In Oracle and DB2 this kind of error treatment was working too. I am not sure how can I do this in PostGreSQL. Does anyone know how can I catch the original PostGreSQL error?
I will give an example from SQL Server:
CREATE PROCEDURE NMSBin_rid (@v_id [NUMERIC](38, 0))
DECLARE @ERROR INT
SELECT @ERROR = 0
IF NOT EXISTS (SELECT * FROM NMSBin WHERE id = @v_id)
BEGIN -- This 50013 is a custom error.
RAISERROR (50013, 16, 1, 'NMSBin_rid')
SELECT * FROM NMSBin WHERE id = @v_id
SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN -- This procedure raise the custom error based on @@ERROR
EXEC ERR_Handle_Errors@ERROR, 'SELECT', 'NMSBin_rid', 'NMSBin'
For my custom treatment of errors I must catch the sql state value and, based on this value, to sent to the application my custom value. Does anyone can give me an example (in a function) of using the concrete value of sql state (not generally terms like not found or something else)?
In DB2 or SQL Server I can based on the exactly values of errors (sql state or @@error).
I found the postgre sql state values in the postgresql documentation but I cannot find any example of using these values. The documentation is a little short and with few and incomplete examples or I mabe didn't descover them.
Thanks in advanced for your help.
RAISE NOTICE "50013: Custom Error from PostgreSQL Server"
I'm just starting with PostgreSQL, too, so this was a WAG.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Thanks. Finally this trick came into my mind too, but is not very elegant.
In JDBC will be extracted the ID (50013) but raise will return the same error id, so in fact this is not a classic raise. A classic raise means, first of all, an ID and this ID to be catch by the application. But in PostGreSQL I don't think is possible. So the only think to be done is to based on the string and catch the id generated in the string.
Any way thanks for your help.