On Dec 19, 5:32 pm, "BigSmoke" <bigsm...@xxxxxxxxx> wrote: > On Dec 19, 5:00 pm, "BigSmoke" <bigsm...@xxxxxxxxx> wrote: > > > > > On Dec 19, 4:16 pm, maili...@xxxxxxxxxxx (Bernd Helmle) wrote: > > > > On 19 Dec 2006 07:05:10 -0800, "BigSmoke" <bigsm...@xxxxxxxxx> wrote: > > > > > I understand that due to a lack of nested transaction support, it is > > > > not possible to use START TRANSACTION within a PL/PgSQL function. What > > > > I, however, do not understand is why I can't use SAVEPOINT either. I'm > > > > writing long test functions wherein, at the start of the function, I'd > > > > like to define all test data followed by a "SAVEPOINT > > > > fresh_test_data;". Will this become possible in the (near) future? I > > > > mean, savepoints are of limited use to me if they imply that I can't > > > > stick my tests in stored procedures.Use > > > > BEGIN > > > > ... > > > > EXCEPTION > > > > ... > > > > END; > > > > Blocks instead. The pl/pgsql exception handling is implemented on top > > > of PostgreSQL's SAVEPOINT infrastructure. We are lacking user defined > > > exception support, but you can raise generic errors with RAISE EXCEPTION.I can't solve my problem with a BEGIN EXCEPTION END block because of > > what I do in these functions. Here's an example function. > > > CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$ > > BEGIN > > -- Define some test data > > > -- SAVEPOINT fresh_test_data; -- If only I could ... > > > IF some_test_assertion_fails THEN > > RAISE EXCEPTION 'Some informative message'; > > END IF; > > > -- ROLLBACK TO SAVEPOINT fresh_test_data; > > END; > > $$ LANGUAGE plpgsql; > > > In these functions, I raise an exception whenever a test fails. Now, If > > I want to create an implicit savepoint using BEGIN/END blocks around > > individual tests, I don't see how I can still sanely preserve this > > behavior without the most horrid of hacks. The following code is what I > > think I would need to do to emulate savepoints without direct access to > > them. :-( (I hope that I'm missing something.) > > > CREATE OR REPLACE FUNCTION test_something() RETURNS VOID AS $$ > > BEGIN > > -- Define some test data > > > BEGIN > > -- This is a useful test ;-) > > IF TRUE THEN > > RAISE EXCEPTION 'Aaargh! The test failed!'; > > END IF; > > > RAISE EXCEPTION '__dummy_restore_state__'; > > > EXCEPTION WHEN raise_exception THEN > > IF SQLERRM != '__dummy_restore_state__' THEN > > RAISE EXCEPTION '%', SQLERRM; > > END IF; > > END; > > END; > > $$ LANGUAGE plpgsql;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.
