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

[Florian G. Pflug]

Emi Lu wrote:

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;

Are you aware of the "insert into <table> (<field1>, ..., <fieldn>) select <val1>, .., 
<valn> from ...."
command? It'd be much faster to use that it it's possible...

greetings, Florian Pflug


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings