使用触发器处理业务表的 lastupdatetimestamp

os: centos 7.4
postgresql: 9.6

数据仓库抽取数据时必然会用到增量方式,如果业务原表没有 lastupdatetimestamp，如果破局？

下面给大家一个思路，仅供参考。

创建表

create table tmp_t0 (
id   int8,
name varchar(100)
);

添加 lastupdatetimestamp 列

alter table tmp_t0 add  lastupdatetimestamp timestamp not null default now();

创建函数

CREATE OR REPLACE FUNCTION f_update_timestamp_column()
RETURNS TRIGGER AS $$
begin
    NEW.lastupdatetimestamp = now(); 
    RETURN NEW;
END;
$$ language 'plpgsql';

创建触发器

必须是 before 触发器

create trigger trg_insert_update_tmp_t0 
before insert or update on tmp_t0 
for each row execute procedure  f_update_timestamp_column(); 

由于源业务库有很多需要改造的表，所以写了个简单的sql。
有动手能力的哥们需要根据情况改造改造。

with tmp_t0 as (
    select pc.relname,
           t0.c1,
           rank() over(partition by pc.relname order by t0.c1) as rk
      from pg_class pc,
           (select 'insert'::varchar as c1 union all select 'update'::varchar as c1) t0  
     where 1=1
       and pc.relnamespace in ( select oid from pg_namespace where nspname in ('public'))
       and pc.relkind in ('r')
       and pc.relname not in ('tmp_t0')
)
select p0.relname,
       ' create trigger trg_'||p0.c1||'_'||p0.relname||
       ' before '||p0.c1||' on '||p0.relname||
       ' for each row execute procedure '||
       case when p0.c1='insert' then ' f_update_timestamp_column(); '
            when p0.c1='update' then ' f_update_timestamp_column(); ' 
            else null
        end as create_trigger,
        ' drop trigger trg_'||p0.c1||'_'||p0.relname||' on '||p0.relname||' ; ' as drop_trigger
  from tmp_t0 p0
 order by p0.relname,
          p0.rk
;