Re: Functions, transactions and RETURN - Mailing list pgsql-sql

From Stewart Ben (RBAU/EQS4) *
Subject Re: Functions, transactions and RETURN
Date
Msg-id E253BDD7F008244585AEE87AF8F0224F116C7A59@cl-mail01.au.bosch.com
Whole thread Raw
In response to Functions, transactions and RETURN  ("Stewart Ben (RBAU/EQS4) *" <Ben.Stewart@au.bosch.com>)
List pgsql-sql
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/


pgsql-sql by date:

Previous
From: "Kenneth Hutchinson"
Date:
Subject: Issue with UPDATE statement on v8
Next
From: Michael Fuhr
Date:
Subject: Re: Issue with UPDATE statement on v8