Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Florian G. Pflug wrote:

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

It did faster. Thank you Florian. Could you hint me why "insert into .. select " is faster than a cursor transaction please?

How about update?

Way1:
update tableA
set col1= X.col1, col2=X.col2, ... coln = X.coln
from table (select ... from ... where ..) AS X
where A.pk = X.pk ;

should be faster than

Way2:
open cursor:
fetch (select ... from ... where ... ) into xCol1, xCol2, ... xColn
   update tableA
   set col1 = xCol1, col2 =xCol2..., coln =xColn
   where tableA.pkCols = xPkCols

right?





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux