Re: in Pl/PgSQL, do commit every 5000 records - Mailing list pgsql-general

Hi Bruno,

>You can't do commits inside of a function.
>

The example I have is:

CREATE OR REPLACE function test() returns boolean AS $$
DECLARE
... ...
   counter                INTEGER := 0;
BEGIN
... ...
   query_value := ' .....' ;
   OPEN curs1 FOR EXECUTE query_value;
   LOOP
      FETCH curs1 INTO studid;
      EXIT WHEN NOT FOUND;

      query_value := ' INSERT INTO ... ...';

      EXECUTE query_value  ;

      counter := counter + 1 ;
      IF counter%5000 = 0 THEN
         counter := 0;
         COMMIT;
      END IF;

   END LOOP;


   CLOSE curs1;
...
END;

... ...

The above function works ok.

"can't do commits inside of a function " , do you mean although the
function complied ok and run successfully, but it did not really commit
insertion actions at every 5000 records?

>I think you are misremembering advice about not do inserts with a transaction per row which will have
>a lot of overhead for all of the commits.
>