Thread: commit inside plpgsql function

Hi,

I am converting oracle stored procedures to plpgsql. There are several 
of the oracle procedures where a parameter vCommit is passed into the 
procedure and:

if vCommit = 1   then       commit;       do_something_commit(vdate);   else       do_something(vdate);   end if;

does this make sense in plpgsql? Does it make sense to do a commit in 
plpgsql?

Thanks,

Dennis

Already been answered (by me) -- you can't do this in postgres 7.x or earlier -- procedures may not have begins/commits
orrollbacks. Version 8.0 *may* be different -- I don't have it installed yet; it does allow for some nesting of
transactionsbut I don't know if this is allowed. The 8.0 documentation on user defined procedures would probably say. 

Greg Williamson
DBA
GlobeXplorer LLC



-----Original Message-----
From:    Dennis Sacks [mailto:dennis@illusions.com]
Sent:    Tue 12/21/2004 9:21 AM
To:    pgsql-sql@postgresql.org
Cc:
Subject:    [SQL] commit inside plpgsql function
Hi,

I am converting oracle stored procedures to plpgsql. There are several
of the oracle procedures where a parameter vCommit is passed into the
procedure and:

if vCommit = 1   then       commit;       do_something_commit(vdate);   else       do_something(vdate);   end if;

does this make sense in plpgsql? Does it make sense to do a commit in
plpgsql?

Thanks,

Dennis

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match