Commit / Rollback in PL/pgSQL ?

[Michael Kleiser]

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

[Tino Wildenhain]

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