PostgreSQL:将两个时间戳设置为彼此相等。其中之一有与之关联的触发器

Question

假设您有一个包含两个 timestamptz 字段的表，created 和updated。

table 有一个触发器，如下所示:

CREATE TRIGGER trig_table_on_update
BEFORE UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE func_table_on_update();

func_table_on_update 定义如下:

CREATE OR REPLACE FUNCTION func_table_on_update()
RETURNS trigger AS
$$
BEGIN
    NEW.updated = current_timestamp;
    RETURN NEW;
END;
$$

如何从另一个函数中设置 created := updated？问题是 created 被设置为 OLD.updated 然后在提交时 OLD.updated 被 current_timestamp 覆盖.我需要在提交后保持两者之间的相等性，但它们不能总是设置为同一时间。

例如，

初始状态:

created            |updated            |
-------------------|-------------------|
2018-10-09 15:59:23|2018-11-12 16:00:22|

更新后:

created            |updated            |
-------------------|-------------------|
2018-11-12 16:00:22|2019-01-09 16:00:22|

期望的状态:

created            |updated            |
-------------------|-------------------|
2018-11-12 16:00:22|2018-11-12 16:00:22|

Answer 1

触发器应该定义为条件:

CREATE TRIGGER trig_table_on_update
BEFORE UPDATE ON my_table
FOR EACH ROW 
WHEN (NEW.created = OLD.created)
EXECUTE PROCEDURE func_table_on_update();

另外，在触发函数中添加语句:

    NEW.created = OLD.updated;

现在交易可能是这样的:

BEGIN;

UPDATE my_table
SET some_column = 'new value'
-- don't set created here!
WHERE id = 1;

SELECT *
FROM my_table;
-- here created <> updated

UPDATE my_table
SET created = updated
WHERE id = 1;
-- the trigger won't be fired

SELECT *
FROM my_table;
-- here created = updated

COMMIT;