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!
FreightTrainAsked:
Who is Participating?
 
earth man2Connect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.