From: | Florent THOMAS <mailinglist(at)tdeo(dot)fr> |
---|---|
To: | postgesql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Attribute a value to a record |
Date: | 2010-02-03 13:42:10 |
Message-ID: | 1265204530.18031.28.camel@Dell_Inspiron |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pedro Zorzenon Neto | 2010-02-03 14:33:59 | Grant on table without access to other tables structure |
Previous Message | Florent THOMAS | 2010-02-03 13:40:12 | Re: Attribute a value to a record |