Postgresql - 更新规则 - 可能有最后修改日期，自动更新该行的“更新”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8740792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql - update rule - possible to have a last modified date, automatically updated "on update" of that row?
提问by rishijd
I want to have a "lastmodified" timestamp (or datetime? not sure if it makes a difference other than presentation of the data) to log the last modified date/time of that record's entry.
我想要一个“上次修改”时间戳（或日期时间？不确定它是否与数据呈现不同）来记录该记录条目的上次修改日期/时间。
Apparently this is possible using triggers. Since I haven't used triggers before, I thought I could first try an "update rule" since that is new to me too:
显然，这可以使用触发器来实现。由于我之前没有使用过触发器，我想我可以先尝试一个“更新规则”，因为这对我来说也是新的：
http://www.postgresql.org/docs/8.3/static/rules-update.html
http://www.postgresql.org/docs/8.3/static/rules-update.html
What I have is this table to log a customer's session data:
我有这个表来记录客户的会话数据：
CREATE TABLE customer_session (
customer_sessionid serial PRIMARY KEY,
savedsearch_contents text,
lastmodified timestamp default now()
); /*
@ lastmodified - should be updated whenever the table is updated for this entry, just for reference.
*/
Then I could create a rule like this. I'm not sure about the syntax, or whether to use NEW or OLD. Could anyone advise the correct syntax?
然后我可以创建这样的规则。我不确定语法，或者是使用 NEW 还是 OLD。谁能建议正确的语法？
CREATE RULE customer_session_lastmodified AS
ON UPDATE TO customer_session
DO UPDATE customer_session SET lastmodified = current_timestamp WHERE customer_sessionid = NEW.customer_sessionid
As you can see I want to update the lastmodified entry of THAT customer_sessionid only, so I'm not sure how to reference it. The UPDATE query would be like this:
如您所见，我只想更新 THAT customer_sessionid 的 lastmodified 条目，因此我不确定如何引用它。UPDATE 查询将是这样的：
UPDATE customer_session SET savedsearch_contents = 'abcde'
WHERE customer_sessionid = {unique customer ID}
Many thanks!
非常感谢！
回答by Kouber Saparev
You cannot do it with a rule, since it would create an infinite recursion. The correct way is to create a before trigger, just as duffymoproposed.
你不能用规则来做，因为它会产生无限递归。正确的方法是创建一个before trigger，就像duffymo建议的那样。
CREATE FUNCTION sync_lastmod() RETURNS trigger AS $$
BEGIN
NEW.lastmodified := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER
sync_lastmod
BEFORE UPDATE ON
customer_session
FOR EACH ROW EXECUTE PROCEDURE
sync_lastmod();
回答by duffymo
You could write a trigger that would fire BEFORE UPDATE to modify that date.
您可以编写一个触发器来触发 BEFORE UPDATE 以修改该日期。
See Example 39-4, which adds user name and timestamp before an UPDATE:
参见例 39-4，它在 UPDATE 之前添加了用户名和时间戳：
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
