Thread: Attribute a value to a record
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
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
Hy
I made an additionnal test
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 :
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; |
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
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
| 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
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
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...
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
| 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.