|
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.
