Dear all,
How to implement transaction in plpgsql?
I have try to use it but fail. And I don't know the reason.
Here is my function...
CREATE FUNCTION sp_templatetouser(INTEGER, INTEGER) RETURNS INTEGER AS '
DECLARE
puserid ALIAS FOR $1;
pplateid ALIAS FOR $2;
rec_affected INTEGER;
BEGIN
BEGIN WORK;
DELETE FROM userrights WHERE userid = puserid;
INSERT INTO userrights SELECT puserid, rightid, allow FROM platedetails
WHERE plateid = pplateid;
GET DIAGNOSTICS rec_affected = ROW_COUNT;
IF rec_affected = 3 THEN
RETURN 1;
ELSE
ROLLBACK WORK;
RETURN 0;
END IF;
COMMIT WORK;
END;
' LANGUAGE 'plpgsql';
best regards,
Corn.