From: | "Stewart Ben (RBAU/EQS4) *" <Ben(dot)Stewart(at)au(dot)bosch(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Functions, transactions and RETURN |
Date: | 2005-09-23 05:55:54 |
Message-ID: | E253BDD7F008244585AEE87AF8F0224F116C7A59@cl-mail01.au.bosch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 may abound.
IF tt_coursearea_name_active(area) THEN
SELECT -2001 INTO errcode; -- E_DUP_COURSEAREA
RAISE EXCEPTION 'Course area "%" already exists.', area;
END IF;
-- Grab a transaction ID
SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid;
IF transid < 0 THEN
SELECT transid 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(dot)stewart(at)au(dot)bosch(dot)com
http://www.bosch.com.au/
From | Date | Subject | |
---|---|---|---|
Next Message | Cédric Buschini | 2005-09-23 11:48:33 | COPY TO / COPY FROM |
Previous Message | Stewart Ben (RBAU/EQS4) * | 2005-09-23 01:50:49 | Functions, transactions and RETURN |