After reading up on Postgres documentation, it seems that transactions
and savepoints are not available to functions, and savepoints are
implemented via BEGIN.. EXCEPTION.. END blocks.
I have a function returning an int4 with the following proposed
structure:
----------------------------------------------------------------------
BEGIN -- Start the transaction, lock tables SAVEPOINT start;
LOCK TABLE backend.table IN ACCESS EXCLUSIVE MODE; LOCK TABLE backend.table2 IN SHARE MODE;
-- Check data in another table. IF NOT tt_check_table2(var1) THEN ROLLBACK TO SAVEPOINT start; RETURN -1; --
E_NO_ACCESSEND IF;
-- Check data in this table. IF tt_check_table(var2) THEN ROLLBACK TO SAVEPOINT start; RETURN -2000; --
E_DUP_COURSEEND IF;
-- <snipped more checks>
-- Insert the row BEGIN INSERT INTO backend.table (foo, bar, baz) VALUES (1, 2, 3);
-- Success RETURN 0;
EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT start; RETURN -32767; -- E_UNKNOWN END;
ROLLBACK TO SAVEPOINT start;
END;
----------------------------------------------------------------------
I can't see how to implement both savepoints and returning distinct
values with the BEGIN.. RAISE EXCEPTION.. EXCEPTION.. END method
proposed in previous mailing list posts.
Are there any suggestions on how to implement this?
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/