Trigger with dynamic field name

Question

I have a problem on creating PostgreSQL (9.3) trigger on update table. I want set new values in the loop as

EXECUTE 'NEW.'|| fieldName || ':=''some prepend data'' || NEW.' || fieldName || ';';

where fieldName is set dynamically. But this string raise error

ERROR:  syntax error at or near "NEW"

How do I go about achieving that?

Answer 1

I found a working solution: trigger should execute after insert/update, not before. Then desired row takes the form

EXECUTE 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME ||
                ' SET ' || fieldName || '= ''prefix:'' ||''' || fieldValue || ''' WHERE id = ' || NEW.id;

fieldName and fieldValue I get in the next way:

FOR fieldName,fieldValue IN select key,value from each(hstore(NEW)) LOOP
       IF .... THEN
END LOOP: