postgresql 当布尔字段设置为 true 时，postgres 更新日期字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1410209/
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
postgres update a date field when a boolean field is set to true
提问by dland
For the sake of the example, consider a table
为了这个例子，考虑一个表
create table foo (
contents text NOT NULL,
is_active boolean NOT NULL DEFAULT false,
dt_active date
)
I insert a record:
我插入一条记录：
insert into foo (contents) values ('bar')
So far, so good. Later on, I now want to 'activate' the record:
到现在为止还挺好。稍后，我现在想“激活”记录：
update foo set is_active = true
What I would like to do when is_activeis changed from falseto true, is for dt_activeis set to now(). For bonus points it would be nice if is_activeis changed from trueto false, dt_active is set to null, but I can live without that.
当is_active从false变为 时true，我想做的是将fordt_active设置为now()。对于奖励积分，如果is_active从更改true为false，dt_active 设置为 null，那就太好了，但我可以没有它。
I'd really like to push this housekeeping into the database, it would make the client code much cleaner (since many tables (and even column tuples within tables) could benefit from this technique).
我真的很想将这种内务管理推送到数据库中，它会使客户端代码更清晰（因为许多表（甚至表中的列元组）都可以从这种技术中受益）。
I'm stumped as to how to pull out the current record in the database in the trigger (I'm using plpgsql), in order to compare the "then" with the "now". Pointers to code examples or snippets greatly appreciated.
我很难过如何在触发器中提取数据库中的当前记录（我正在使用 plpgsql），以便将“then”与“now”进行比较。非常感谢指向代码示例或片段的指针。
回答by cms
Inside your plpgsql trigger procedure you have access to some special record-type variables called NEWand OLDthat are created for you.
您PLPGSQL触发器过程里你有机会获得所谓的一些特殊的记录类型变量NEW和OLD那些为您创建。
In an UPDATEor INSERTtrigger , NEWwill represent the record of the new database row.
在UPDATEorINSERT触发器中，NEW将代表新数据库行的记录。
In an UPDATEor DELETEtrigger , OLDwill represent the value of the original database row.
在UPDATEorDELETE触发器中，OLD将代表原始数据库行的值。
In other statement contexts, these record variables will be NULL.
在其他语句上下文中，这些记录变量将为NULL.
Therefore, it seems like you need to create an INSERT OR UPDATEtrigger that looks at the values of OLD.is_activeand NEW.is_active.
因此，它好像你需要创建一个INSERT OR UPDATE触发器，着眼于价值OLD.is_active和NEW.is_active。
Here's the documentation page - http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.htmlThis page contains sample code for plpgsql that uses NEWand OLD
这是文档页面 - http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html此页面包含使用NEW和OLD
回答by cms
CREATE OR REPLACE FUNCTION trg_update_foo() RETURNS TRIGGER AS
$BODY$
BEGIN
IF OLD.is_active = false AND NEW.is_active = true THEN
NEW.dt_active := now();
ELSIF OLD.is_active = true AND NEW.is_active = false THEN
NEW.dt_active := NULL;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_update_foo BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE trg_update_foo();
Should work. For more information, check pl/PgSQL Triggers manual, and for extra points - whole plPgSQLdocs.
回答by Frank Bollack
Have you tried the manual?
你试过手册吗？
Every trigger function has some automatic variables. In case of update triggers the old rows values are accessable via OLDand the changed values via NEW
每个触发函数都有一些自动变量。在更新触发器的情况下，旧行值可通过访问OLD，更改值可通过NEW
You can simply check the column value of OLD by OLD.is_active. The same way you can change the values to be put in the database like NEW.dt_active := now();
您可以通过 简单地检查 OLD 的列值OLD.is_active。您可以使用相同的方式更改要放入数据库的值，例如NEW.dt_active := now();
Hope this hels.
希望这会有所帮助。
回答by Michael Krelin - hacker
CREATE FUNCTION actrigger() RETURNS TRIGGER AS $$ BEGIN
IF TG_OP='UPDATE' THEN
IF NEW.is_active THEN
IF NOT OLD.is_active THEN
NEW.dt_active := current_date;
END IF;
ELSIF NEW.dt_active IS NOT NULL
NEW.dt_active := NULL;
END IF;
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER foobefore BEFORE UPDATE ON foo FORR EACH ROW
EXECUTE PROCEDURE actrigger();
And you can take care of INSERTtoo which would be pretty similar except for it shouldn't refer to OLD.
而且你也可以照顾它，INSERT这将非常相似，除了它不应该引用OLD.
