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?
