Re: transaction management in plpgsql functions

After takin a swig o' Arrakan spice grog, Cris Carampa <cris119(at)operamail(dot)com> belched out...:
> It seems that transaction management statements (begin work...commit
> work) are not allowed into plpgsql functions. Is it true? If true,
> what happens if I put a DML statement into a function? Is it
> automatically commited every time the function executes? Is there no
> way to rollback the changes?

The "problem" with using BEGIN/COMMIT in plpgsql is fundamentally that
those functions have to be _started_ in the context of a transaction,
so by the time they get started, there is already a transaction in
progress.

If-and-when support for nested transactions gets into place, you would
presumably be able to have nested transactions inside functions.

What happens may be a little different from what you think; things are
not COMMITted when the function executes, but rather when the COMMIT
takes place /on the transaction in which the function runs/.

Thus...

BEGIN;
INSERT INTO T1 (4, 5);
INSERT INTO T2 (6, 7, NOW());
SELECT FUNNY_FUNCTION(4,5,6,7, NOW());
DELETE FROM T1;
DELETE FROM T2;
COMMIT;

All of the changes commit as of the COMMIT statement at the end, and
not before.

If you had DML creating table T3 in FUNNY_FUNCTION, then T3 would not
become visible to other users until the COMMIT, although the current
transaction could readily add/modify records before the COMMIT.
--
(format nil "~S(at)~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/sap.html
If you're sending someone some Styrofoam, what do you pack it in?