Thanks a lot for this precision. unfortunately, the cursor doesn't accept "complicated" queries whereas record type stay more powerfull on this aspect. I found a solution and BTW it has considerably simplfy my code! A clue can make you think better!
Le mercredi 03 février 2010 à 14:33 +0100, Wappler, Robert a écrit : > On 2010-02-03, Florent THOMAS wrote: > > > Dear laurenz Albe, > > > > Thank you for answering so fast. for me, the variable ventilation_local > > is defined as a record type. So as I wrote on the other mail, I made > > some additionnal test because the doc precise that the syntax above is > > allowed : http://www.postgresql.org/docs/8.4/interactive/plpgsql-stateme > > nts.html#PLPGSQL-STATEMENTS-ASSIGNMENT (last line) > > > > I understood that in the Loop you can change the values of a > > variable! Exactly what I needed. > > but unfortunately all of this seems to be temporary. > > Consequently, the record in the table won't be updated by the > > changes we made on the local variable even if it points to a > > record in the table. > > I forgot the aspect of the cursor that is temporary. > > > > But in all the case, It could be a great improvement to let > > the syntax modify directly the table. > > > > I think I will find another way to do it. with EXECUTE!! > > > > Best regards > > > > Le mercredi 03 février 2010 à 10:05 +0100, Albe Laurenz a écrit : > > > > > > Florent THOMAS wrote: > > > I'm currently running on pg8.4 and I have a trigger > > with a loop : > > > > > > FOR ventilation_local IN (SELECT * FROM XXX) LOOP > > > IF (mytest) THEN > > > ventilation_local.myfield:=mynewvalue; > > > END IF; > > > END LOOP; > > > > > > my problem is that the record doen't accept the new value. > > > I've chek before the value that is not null. > > > Is it a fonctionnality accepted in pg8.4 on record type? > > > > What do you mean by "the record doen't accept the new value"? > > > > Can you show us some SQL statements that exhibit the problem? > > > > Yours, > > Laurenz Albe > > > > > > A record variable is not a physical record. It is a type consisting of some > fields. > > DECLARE > ventilation_local refcursor FOR SELECT * FROM XXX; > BEGIN > OPEN ventilation_local; > MOVE ventilation_local; > WHILE FOUND LOOP > UPDATE XXX SET myfield = mynewvalue WHERE CURRENT OF ventilation_local; > MOVE ventilation_local; > END LOOP; > END; > > This way, ventilation_local is not a record variable, but a cursor, which is > indeed updatable. >
