• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 862
  • Last Modified:

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!
0
FreightTrain
Asked:
FreightTrain
  • 2
1 Solution
 
ivanovnCommented:
Here is a document describing everything you know regarding error handling in PostgreSQL:
http://www.postgresql.org/docs/8.2/static/ecpg-errors.html
0
 
FreightTrainAuthor Commented:
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
0
 
FreightTrainAuthor Commented:
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...
0
 
earth man2Commented:
CREATE OR REPLACE FUNCTION error_test_demo ( mode char(1)  ) RETURNS int AS $$
 DECLARE err int := 0;
 BEGIN
  begin  
    select 1;
     IF ( NOT FOUND ) THEN
        err := -1013;
    END IF;
  exception
    when others then
      NULL;
  end;
  return err;
end; $$ language plpgsql;

estdb=> select error_test_demo('t');
 error_test_demo
-----------------
               0
(1 row)

transaction control is done outside the plpgsql stored functiion
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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