FreightTrain
asked on
Transaction with Error catch and Rollback code
I am new to PostgreSQL. I need to know the proper syntax for encompassing my stored procedures and SQL Scripts into a Transaction and how to properly catch an error and rollback. Please Help!
ASKER
I read this page, but am still having difficulties understanding. There is not a good example. I come from a T-SQL background and to write a stored proc encompassed in a transaction with error handling, the format would be like this.
CREATE PROCEDURE error_test_demo @mode char(1) AS
DECLARE @err int
BEGIN TRANSACTION
-- DO MULTIPLE UPDATE STATMENTS HERE --
-- AFTER EACH STATEMENT, CHECK FOR AN ERROR USING THE CODE BELOW--
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
CREATE PROCEDURE error_test_demo @mode char(1) AS
DECLARE @err int
BEGIN TRANSACTION
-- DO MULTIPLE UPDATE STATMENTS HERE --
-- AFTER EACH STATEMENT, CHECK FOR AN ERROR USING THE CODE BELOW--
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
ASKER
What I am looking for is the PostgreSQL equivilant to the Transaction and Error handling shown above. I hope this is more clear on what I need. Thanks in advance for you continued help on this...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.postgresql.org/docs/8.2/static/ecpg-errors.html