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

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?