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_ACCESS
END IF;
-- Check data in this table.
IF tt_check_table(var2) THEN
ROLLBACK TO SAVEPOINT start;
RETURN -2000; -- E_DUP_COURSE
END 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:[EMAIL PROTECTED]
http://www.bosch.com.au/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
