Thread: SQL error

HI everyone,


I´m trying to capture all the possible errors that a statement can have. And, if there´s any error i will do a rollback; What i´m trying to do its:
BEGIN
  insert into temp values(1, 2, 3);
  IF ANY_ERROR_OCCURED THEN
     ROLLBACK;
     RETURN FALSE;
  END IF;
END;

Did anyone knows how can i do that?

Thanks a lot!

On Sat, Apr 19, 2008 at 11:47:19AM -0300, x asasaxax wrote:
> I´m trying to capture all the possible errors that a statement can have.
> And, if there´s any error i will do a rollback; What i´m trying to do its:

What are you trying to do? Any error automatically rolls back the
transaction, so:

> BEGIN
>   insert into temp values(1, 2, 3);
> END;

Should do what you want.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

x asasaxax wrote:
> HI everyone,
>
>
> I´m trying to capture all the possible errors that a statement can have.
> And, if there´s any error i will do a rollback; What i´m trying to do its:
> BEGIN
>   insert into temp values(1, 2, 3);
>   IF ANY_ERROR_OCCURED THEN
>      ROLLBACK;
>      RETURN FALSE;
>   END IF;
> END;
>
> Did anyone knows how can i do that?
>
Use a PL/pgSQL stored procedure. PostgreSQL does not support ad-hoc
blocks or unnamed procedures, but it's OK to create the procedure, use
it, and drop it again.

http://www.postgresql.org/docs/current/static/plpgsql.html

and particularly:

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

However, the EXCEPTION blocks you use to trap errors aren't free in
performance terms, so you should probably look for other approaches first.

--
Craig Ringer

x asasaxax wrote:
> HI everyone,
>
>
> I´m trying to capture all the possible errors that a statement can have.
> And, if there´s any error i will do a rollback; What i´m trying to do its:
> BEGIN
>   insert into temp values(1, 2, 3);
>   IF ANY_ERROR_OCCURED THEN
>      ROLLBACK;

Sorry, I didn't see the ROLLBACK statement there. Please disregard my
previous reply; I thought you were trying to do something other than
what you appear to be doing.

As Martijn van Oosterhout noted, the transaction will be put in an error
state that ignores further commands. When in an error state, the
transaction will treat COMMIT as ROLLBACK.

I assume you wish to attempt to do something and if it fails, abort the
transaction and leave everything in a working state ready for more
commands? If so, consider doing that at the application level rather
than trying to do it in SQL. All database interfaces give you a way to
find out if an error occurred and get some information about the error.

Maybe if you explained your end goal and why you're trying to do this it
might help people give you more useful answers?

--
Craig Ringer