Re: Anybody have an Oracle PL/SQL reference at hand?

On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote:
> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>
> > BEGIN;
> > SAVEPOINT start;
> > INSERT INTO users VALUES(user || suffix);
> > EXIT;
> > EXCEPTION
> > WHEN UNIQUE_VIOLATION THEN
> > ROLLBACK TO start;
> > suffix := suffix + 1;
> > END;
>
> Right. Essentially, our implementation is supplying the SAVEPOINT and
> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION
> clause. When we get around to updating the "Oracle porting" guide in
> the plpgsql docs, this will need to be clearly explained.
>
> Depending on how tense you want to be about Oracle compatibility, we
> could make people actually write their blocks as above --- that is,
> the SAVEPOINT and ROLLBACK commands would be a required part of the
> exception-block syntax. They wouldn't actually *do* anything, but
> they would make the code look more like its Oracle equivalent. I'm not
> for this, but maybe someone wants to make the case for it?
>
> regards, tom lane

If it's not difficult it would probably be good to allow for handling
the rollback yourself. In this example it wouldn't matter; the row
triggering an error won't be inserted. But if you were inserting data
from a multi-row source such as a temporary table it would make a
difference.

By the way, while I know Oracle won't abort the transaction, they might
rollback whatever work the command that failed had done; I'm not really
sure how that's handled.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"