Thread: Commit / Rollback in PL/pgSQL ?

I found on
http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/plpgsql-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

Am Mi, den 13.10.2004 schrieb Michael Kleiser um 17:44:
> I found on
> http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/plpgsql-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