Thread: Transactions inside plpgsql?
Hi I've been searching the web/mailing lists/web boards for the answer to a question I have regarding postgresql. So far, I haven't found a conclusive answer. My apologies in advance if this is a FAQ/stupid question. My problem is that I would like to write a plpgsql procedure that starts and ends transactions. This requires use of the postgres keyword 'begin'. From what I can see, the plpgsql parser is wrongly interpreting the begin as a plpgsql keyword. I considered the idea of creating an 'sql' language stored procedure, called say begin_work() which in turn makes the 'begin work' statement. While this does seem to work, it seems a bit rediculous. I'm hoping that I've been stupid and missed something obvious :-) If someone could point me in the direction of the correct documentation, I'd be most grateful. Thanks! Cheers, Alan -- ----------------------------------------------------------------------------- Alan Turner | Voice: (02) 9481 8223 | Email: alan@suburbia.com.au ----------------------------------------------------------------------------- Encrypted mail welcome. | Key: http://homepages.tig.com.au/~aturner/alan.gpg 1024D/9F513D15 1999-09-16 Alan Turner <alan@suburbia.com.au> Key fingerprint = 2CC3 CDB9 7BE2 0585 EA63 19A1 C4EC EA32 9F51 3D15 ----------------------------------------------------------------------------- To see a need and wait to be asked, is to already refuse. ----------------------------------------------------------------------------- --FAA06171.980657327/dshmail1.dsh.de-- -- ----------------------------------------------------------------------------- Alan Turner | Voice: (02) 9481 8223 | Email: alan@suburbia.com.au ----------------------------------------------------------------------------- Encrypted mail welcome. | Key: http://homepages.tig.com.au/~aturner/alan.gpg 1024D/9F513D15 1999-09-16 Alan Turner <alan@suburbia.com.au> Key fingerprint = 2CC3 CDB9 7BE2 0585 EA63 19A1 C4EC EA32 9F51 3D15 ----------------------------------------------------------------------------- To see a need and wait to be asked, is to already refuse. -----------------------------------------------------------------------------
Basically, answer is: you can't do it, no matter how you slice it, for a simple reason: postgres doesn't support nested transactions [yet?]. On Sun, 28 Jan 2001, Alan Turner wrote: > Hi > > I've been searching the web/mailing lists/web boards for the answer to a > question I have regarding postgresql. So far, I haven't found a conclusive > answer. My apologies in advance if this is a FAQ/stupid question. > > My problem is that I would like to write a plpgsql procedure that starts and > ends transactions. This requires use of the postgres keyword 'begin'. From > what I can see, the plpgsql parser is wrongly interpreting the begin as a > plpgsql keyword. > > I considered the idea of creating an 'sql' language stored procedure, called > say begin_work() which in turn makes the 'begin work' statement. While this > does seem to work, it seems a bit rediculous. > > I'm hoping that I've been stupid and missed something obvious :-) > > If someone could point me in the direction of the correct documentation, > I'd be most grateful. > > Thanks! > > Cheers, > Alan > > > >