用动态字段名触发 [英] Trigger with dynamic field name

问题描述

我在更新表上创建PostgreSQL(9.3)触发器时遇到问题. 我想在循环中将新值设置为

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 || ';';

其中fieldName是动态设置的.但是此字符串引发错误

where fieldName is set dynamically. But this string raise error

ERROR:  syntax error at or near "NEW"

我该如何实现?

推荐答案

我找到了一个可行的解决方案: 触发器应在插入/更新之后而不是之前执行.然后所需的行采用以下形式

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和fieldValue我以另一种方式获得:

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:

这篇关于用动态字段名触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！