Re: Savepoints in PL/pgSQL

[Bernd Helmle <mailings(at)oopsware(dot)de>]

On 19 Dec 2006 08:37:01 -0800, "BigSmoke" <bigsmoke(at)gmail(dot)com> wrote:

[...]

What would solve my problem is if there was a
> method to, at the end of
>> a begin/end block, I could rollback the changes made in that block
>> without having to raise an exception. Is it somehow possible to
>> explicitly rollback to one of these savepoints which are created by
>> begin/end blocks?
>
> I'm sorry for the sloppy English. Of course I meant to say "at the end
> of a begin/end block, rollback" instead of "at the end of a begin/end
> block, I could rollback". Hopefully, this didn't add in the confusion.

If you are hoping you could adopt exact Oracle behavior, i have to regret,
you have to do as you've already done. There's no such thing like 'explicit
transaction control' in plpgsql. Wouldn't it be easier to wrap your tests into
self-contained testfunctions like

SAVEPOINT A;
SELECT testcase1();
ROLLBACK TO A;
SELECT testcase2();
ROLLBACK TO A;
...

and to evaluate return codes into your application? Maybe someone knows better, but
that's the only solution that comes to my mind off-hand....

Bernd