>I have an other question.
>
>How can i rollback ALL the query when one of these return an error?
>
>My code is like the following:
>
>------------------------------------
>BEGIN
>
>INSERT INTO table_1 ....
>
>INSERT INTO table_2 ....
>
>INSERT INTO table_3 ....
>
>EXCEPTION WHEN others THEN
> code:=SQLSTATE;
> mess:=SQLERRM;
>
>es:=code||'|'||mess;
>
>
>RETURN es;
>
>END;
>------------------------------------
>
>In this case when an error occurs the rollback work only on the wrong query. The other insert are committed.
The rollback only takes place on the errored statement, because you are catching the exception.
In order to ensure a complete rollback of your transaction (which may have started outside of your function),
you'll need to rethrow an error after your exception handling.
In order to store a corresponding message within a table, you'll need a separate transaction which can be achieved,
- as already mentioned- with dblink. Possibly some <pg>_fwd extensions allow this too, I don't know.
regards,
Marc Mamin