Attribute a value to a record

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 :

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

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 :