Thread: SET TRANSACTION on pl/pgSQL function

Hi to all,
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.

PLease could someone help me to understand  by some example how to do?

Thank you very much
Bye Gianni




Tiscali ADSL 4 Mega Flat
Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese!
Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE.

http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14

On Thu, Mar 02, 2006 at 12:34:25AM +0100, gianni.zammarchi@tiscali.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