How to implement transaction in plpgsql?
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.
"Corn" <corn@tryit.com> writes:
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...
You can't use transactions inside a function. The reason is that when
a function executes you are already inside a transaction (every SQL
statement is a transaction if there is no explicit BEGIN) and PG
doesn't support nested transactions.
See the FAQ for more info; I'm pretty sure this issue is in there (if
not, it should be).
-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/
We will return to our regularly scheduled signature shortly.
Import Notes
Reply to msg id not found: Corn'smessageofFri14Sep2001130527+0800
"Corn" wrote:
Dear all,
How to implement transaction in plpgsql?
I have try to use it but fail. And I don't know the reason.
Check the documentation again. It is not supported. (There are no
nested transactions in PostgreSQL.)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But without faith it is impossible to please him; for
he that cometh to God must believe that he is, and
that he is a rewarder of them that diligently seek
him." Hebrews 11:6
If I remember correctly, the document in "function"
section sas that people can not use transaction
statements (begin, commit, rollback) IN any function.
Instead, we should use these transaction statements
OUTSIDE function.
Good luck.
CN
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
Import Notes
Resolved by subject fallback