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?