Re: Update and cursor

Patrick COLLIN <patrick(at)felixfr(dot)com> writes:
> I first tried a global update on each column, but I have not enough
> memory and swap to do that.

> FOR nouvEnreg IN SELECT * FROM mfnf00 LOOP
> nouvCoupal := 2 * nouvEnreg.coupal;
> UPDATE mfnf00 SET coupal = nouvCoupal
> WHERE cbase = nouvEnreg.cbase AND
> satel = nouvEnreg.satel AND
> citm8 = nouvEnreg.citm8;
> END LOOP;

I think the problem here is not so much the UPDATEs as it is the
SELECT; IIRC, plpgsql will try to fetch the whole result of the
select into memory before it starts to run the loop. You could
work around that, I think, by using a cursor to fetch the rows
one at a time. But in this case, you're just coding a gratutiously
inefficient way of doing a global update: why not replace the
whole loop with

UPDATE mfnf00 SET coupal = 2 * coupal;

which will be vastly faster as well as not having a memory issue.

regards, tom lane