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.