Thread: Attribute a value to a record

Attribute a value to a record

From
Florent THOMAS
Date:
Hello,

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?

Best regards

Re: Attribute a value to a record

From
Florent THOMAS
Date:
Hy

I made an additionnal test
FOR ventilation_local IN (SELECT * FROM XXX) LOOP
     IF (mytest) THEN 
          RAISE NOTICE 'ventilation %',  ventilation_local;
          ventilation_local.myfield:=10;  
          RAISE NOTICE 'ventilation %',  ventilation_local;
      END IF;
END LOOP;

the first notice  and the second one are different.
Unfortunately, when I get out from the LOOP, the result doesn't seems to be updated in the table

Best regards


Le mercredi 03 février 2010 à 00:28 +0100, Florent THOMAS a écrit :
Hello,

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?

Best regards

Re: Attribute a value to a record

From
"Albe Laurenz"
Date:
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

Re: Attribute a value to a record

From
Florent THOMAS
Date:
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-statements.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

Re: Attribute a value to a record

From
"Albe Laurenz"
Date:
Florent THOMAS wrote:
> 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.

I get you now - you expected that the underlying table would
be updated if you change a variable in PL/pgSQL.

I don't think that you need dynamic SQL for that -
all it takes is an UPDATE statement in your loop, like

UPDATE XXX SET XXX.myfield = mynewvalue
   WHERE XXX.pkey = ventilation_local.pkey;

(using the names from your sample)

Yours,
Laurenz Albe

Re: Attribute a value to a record

From
"Wappler, Robert"
Date:
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.

--
Robert...



Re: Attribute a value to a record

From
Florent THOMAS
Date:


Le mercredi 03 février 2010 à 14:38 +0100, Florent THOMAS a écrit :
Thank you,

As I posted on a french list, whene I start to develop trigger bigger than 10lines, I always come back on developper habits and forget database aspects.
So I WILL PRINT IT ON MY WALL : With records everything is temporary.

Best regards and sorry for the english!



Le mercredi 03 février 2010 à 14:13 +0100, Albe Laurenz a écrit :
Florent THOMAS wrote:
> 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.

I get you now - you expected that the underlying table would
be updated if you change a variable in PL/pgSQL.

I don't think that you need dynamic SQL for that -
all it takes is an UPDATE statement in your loop, like

UPDATE XXX SET XXX.myfield = mynewvalue  WHERE XXX.pkey = ventilation_local.pkey;

(using the names from your sample)

Yours,
Laurenz Albe

Re: Attribute a value to a record

From
Florent THOMAS
Date:
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.