网站的二维码怎么做的/网站推广教程

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 $$
beginNEW.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 rkfrom pg_class pc,(select 'insert'::varchar as c1 union all select 'update'::varchar as c1) t0  where 1=1and 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 nullend as create_trigger,' drop trigger trg_'||p0.c1||'_'||p0.relname||' on '||p0.relname||' ; ' as drop_triggerfrom tmp_t0 p0order by p0.relname,p0.rk
;