Re: Attribute a value to a record

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