PgAUT插件的原理-阿里云开发者社区

开发者社区> 数据库> 正文

PgAUT插件的原理

简介: 在PostgreSQL中实现MySQL的on update current_timestamp功能

在MySQL中,如果一个列的类型是TIMESTAMPDATETIME,并且设置了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 TABLEALTER TABLEDROP 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

版权声明:本文首发在云栖社区,遵循云栖社区版权声明:本文内容由互联网用户自发贡献,版权归用户作者所有,云栖社区不为本文内容承担相关法律责任。云栖社区已升级为阿里云开发者社区。如果您发现本文中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,阿里云开发者社区将协助删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章