Thread: Functions, transactions and RETURN

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/

Hate to reply to my own posts, but I thought the solution I've come
across may help others with problems implementing savepoints and
transactions in functions.

This function implements rollbacks whilst still returning a valid row
instead of an exception. A temporary variable is used to get around the
ugly lack of SAVEPOINTs.

----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tr_addcoursearea(employeeno, coursearea) RETURNS int4 AS
$BODY$DECLARE  transid int4;  errcode int4;
BEGIN -- Setup default return code. This is used if we hit an -- exception that we didn't throw. SELECT -32767 into
errcode; -- E_UNKNOWN 
 LOCK TABLE backend.courseareas IN ACCESS EXCLUSIVE MODE;
 -- Access to administrators only IF NOT tt_user_access(actor, 'a') THEN   SELECT -1 into errcode;  -- Return
E_NO_ACCESS  RAISE EXCEPTION 'User % does not have access.', actor; END IF; 
 -- Check if there are any active course areas with -- the given name. We do not allow duplicate names.. -- confusion
mayabound. IF tt_coursearea_name_active(area) THEN   SELECT -2001 INTO errcode;  -- E_DUP_COURSEAREA   RAISE EXCEPTION
'Coursearea "%" already exists.', area; END IF; 
 -- Grab a transaction ID SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid; IF transid < 0 THEN
SELECTtransid into errcode; -- Return the error code.   RAISE EXCEPTION 'Could not acquire transaction.'; END IF; 
 -- Insert the row INSERT INTO backend.courseareas   (transactionid, active, caname)   VALUES (transid, TRUE, area);
 RETURN 0; -- SUCCESS

EXCEPTION WHEN RAISE_EXCEPTION THEN   RETURN errcode;
 WHEN OTHERS THEN   RETURN -32767;  -- E_UNKNOWN

END;$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
----------------------------------------------------------------------

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/