PostgreSQL当前时间戳在更新 [英] Postgresql Current timestamp on Update

问题描述

以下mysql代码的postgres等效项是什么

CREATE TABLE t1 (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE t2 (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

按照下面的Alex Brasetvik的回答，看来我应该使用触发器，我的问题是我有许多具有创建和修改字段的表t1，t2 ...，是否可以编写一个通用过程? >

-更新 快准备好了

CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
    BEGIN
        NEW.modified := current_timestamp;
        RETURN NEW;
    END;
$update_timestamp$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t1
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t2
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

解决方案

只需确保所有表具有相同的列名:

CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
BEGIN
    NEW.modified = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER t_name
  BEFORE UPDATE
  ON tablename
  FOR EACH ROW
  EXECUTE PROCEDURE upd_timestamp();

What is the postgres equivalent of the below mysql code

CREATE TABLE t1 (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE t2 (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

As per Alex Brasetvik answer below, it seems i should go with triggers, my problem is i have a number of tables t1, t2... with created and modified fields, is it possible to write a generalized procedure?

--update Almost ready

CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
    BEGIN
        NEW.modified := current_timestamp;
        RETURN NEW;
    END;
$update_timestamp$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t1
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t2
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

解决方案

Just make sure all tables have the same columnname:

CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
BEGIN
    NEW.modified = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER t_name
  BEFORE UPDATE
  ON tablename
  FOR EACH ROW
  EXECUTE PROCEDURE upd_timestamp();

这篇关于PostgreSQL当前时间戳在更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！