Re: Attribute a value to a record - Mailing list pgsql-general

From Florent THOMAS
Subject Re: Attribute a value to a record
Date
Msg-id 1265204530.18031.28.camel@Dell_Inspiron
Whole thread Raw
In response to Re: Attribute a value to a record  ("Wappler, Robert" <rwappler@ophardt.com>)
List 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.

pgsql-general by date:

Previous
From: Florent THOMAS
Date:
Subject: Re: Attribute a value to a record
Next
From: vijayalakshmi thiruvengadam
Date:
Subject: