Re: Commit / Rollback in PL/pgSQL ?

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