Re: in Pl/PgSQL, do commit every 5000 records

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.
>