By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

Commit / Rollback in PL/pgSQL ?

P: n/a
I found on
http://www.physiol.ox.ac.uk/Computin...l-porting.html
that it is not poosible to use start or end a transaction in plpgsl.

I tried to create a plplsql-function on PostgreSQL 8.0 beta 3
I can comile

CREATE OR REPLACE FUNCTION insert_many_commit( integer ) RETURNS void AS '
DECLARE
counter INTEGER := $1;
BEGIN
WHILE counter > 0 LOOP
INSERT INTO testtab (id, modification_date, description )
VALUES ( NEXTVAL(''seq_testtab''),now(), ''Eintrag von insert_many() '' || counter );
COMMIT;
counter := counter-1;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

So I think it's possible to have COMMIT / ROLLBACK in PLPgSQL

But I can't execute this funktion this way:
# select insert_many_commit(1000);
ERROR: SPI_execute_plan failed executing query "COMMIT": SPI_ERROR_TRANSACTION
Is there an other way to execute tis function ?
If the latter, is it poosible in other languages like PL/Python or PL/Perl ?

regards
Michael Kleiser

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Am Mi, den 13.10.2004 schrieb Michael Kleiser um 17:44:
I found on
http://www.physiol.ox.ac.uk/Computin...l-porting.html
that it is not poosible to use start or end a transaction in plpgsl.

I tried to create a plplsql-function on PostgreSQL 8.0 beta 3
I can comile

CREATE OR REPLACE FUNCTION insert_many_commit( integer ) RETURNS void AS '
DECLARE
counter INTEGER := $1;
BEGIN
WHILE counter > 0 LOOP
INSERT INTO testtab (id, modification_date, description )
VALUES ( NEXTVAL(''seq_testtab''),now(), ''Eintrag von insert_many() '' || counter );
COMMIT;
counter := counter-1;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

So I think it's possible to have COMMIT / ROLLBACK in PLPgSQL


No, you cant. The whole execution is part of one statement which is
then automatically encapsulated in one transaction. Maybe the
checkpoint features of the upcoming pg8.x help you.

Otoh, why do you want to commit here anyway?

Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.