在MySQL中，如果一个列的类型是TIMESTAMP或DATETIME，并且设置了on update current_timestamp，则该字段的值会随着update命令更新行记录时自动更新成当前时间。这个特性在应用开发中非常有用，方便记录每一行最后的更新时间。

但这个特性不是标准SQL语法，数据库迁移到PostgreSQL后不得不在每条update语句里添加set updated_at = current_timestamp，难免会有遗漏。因此，我开发了PgAUT插件，提供类似MySQL的功能。

一、标记自动更新字段

插件的原理是在创建表的时候，自动创建一个与之相应的trigger，在更新语句执行之前把需要自动更新的字段的值统一赋值为clock_timestamp()。

为了能标记出哪些字段是需要自动更新，使用“域”为timestamp类型创建了一个特殊的别名，后续用这个别名作为需要自动更新字段的类型：

create domain auto_update_timestamp as timestamp;

-- 创建表
create table foo (
  id bigint primary key,
  c1 auto_update_timestamp default current_timestamp
);
-- 修改表
alter table foo add column c2 auto_update_timestamp;

二、创建触发器（Trigger）

知道了哪些列需要自动更新后，可以给表绑定一个触发器，在更新的时候自动更新值：

create or replace foo_on_update_handler() returns trigger as 
$$

begin
  new.c1 = clock_timestamp();
  new.c2 = clock_timestamp();
  return new;
end;

$$
 language plpgsql;

create trigger foo_on_update_trigger
 before update on foo
 for each row execute
 procedure foo_on_update_handler();

三、事件触发器（Event Trigger）

有别与普通的触发器，事件触发器能捕捉所有DDL的变动，例如CREATE TABLE、ALTER TABLE、DROP TABLE等。利用这个特性，就能实现在创建表的时候，自动创建与之相应的触发器：

create or replace function table_event_trigger_handler()
 returns event_trigger as 
$$

declare
  _e record;
  _sql text;
  _schema_name text;
  _table_name text;
begin
  -- 通过 pg_event_trigger_ddl_commands 获取当前变更的元素信息
  for _e in select * from pg_event_trigger_ddl_commands() loop
    if _e.object_type = 'table' and _e.command_tag = 'CREATE TABLE' then
      -- 从 pg_event_trigger_ddl_commands 无法拿到表的名称
      -- 只能手工从 pg_class 中获取 schema 和 table 的名称
      select
        pg_namespace.nspname,
        pg_class.relname
      into
        _schema_name,
        _table_name
      from
        pg_class
      inner join
        pg_namespace
      on
        pg_class.relnamespace = pg_namespace.oid
      where
        pg_class.oid = _e.objid;

      -- 从列元信息表中获得变更表的所有类型为 auto_update_timestamp 的列名
      -- 拼装成一组 new.<column-name> := clock_timestamp();
      select
        string_agg('  new.' || column_name || ' := clock_timestamp();', E'\n')
      into
        _sql
      from
        information_schema.columns
      where
        table_schema = _schema_name
        and table_name = _table_name
        and domain_name = 'auto_update_timestamp';

      -- 执行动态语句，创建触发器函数
      execute format($SQL$
create or replace function %s.%s_on_update_handler() returns trigger as $HANDLER$
begin
  %s
  return new;
end;
$HANDLER$ language plpgsql;
$SQL$, _schema_name, _table_name, _sql);

      -- 执行动态语句，创建触发器
      execute format($SQL$
create trigger %s_%s_on_update_trigger
 before update on %s.%s
 for each row execute
 procedure %s.%s_on_update_handler()
$SQL$, _schema_name, _table_name
     , _schema_name, _table_name
     , _schema_name, _table_name);

    end if;
  end loop;
end;

$$
 language plpgsql;

-- 创建事件触发器，处理CREATE TABLE事件
create event trigger table_event_trigger
  on ddl_command_end
  when tag in ('CREATE TABLE')
  execute procedure table_event_trigger_handler();

四、补齐其他事件触发器

上述代码实现了CREATE TABLE时自动创建触发器函数与触发器，并绑定到新建的表上，还需要创建ALTER TABLE处理添加或删除表字段时更新触发器函数，以及DROP TABLE处理时删除触发器函数。

细节可以参考插件的源码：https://github.com/redraiment/pgaut/blob/master/pgaut--1.0.0.sql

PgAUT插件的原理

一、标记自动更新字段

二、创建触发器（Trigger）

三、事件触发器（Event Trigger）

四、补齐其他事件触发器

相关文章：