Re: SET TRANSACTION on pl/pgSQL function

On Thu, Mar 02, 2006 at 12:34:25AM +0100, gianni(dot)zammarchi(at)tiscali(dot)it wrote:
> I have a little problem, I am working with postgres 8.1.2 and I am
> creating some store procedure, I would like to handle inside to them
> the commit and rollback functionality, but If I entry commit command I
> am not able to exceute the funciotn anymore.

PostgreSQL functions are executed in the context of an outer
transaction so they can't issue COMMIT or ROLLBACK statements.
However, in 8.0 and later, several languages (e.g., PL/pgSQL)
support error trapping, effectively providing savepoint and
rollback to savepoint capability.

http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

One way around this restriction would be for the function to use
dblink to make another connection to the database; the server-side
function then becomes a database client. The function could execute
transaction-starting and -ending commands over that connection.

--
Michael Fuhr