APP事件表通用字段视图的实现

--两个app的事件表，假定字段名称不同
create table app1_evt(start_time timestamp);
insert into app1_evt values('2016-05-18 10:30:00');
create table app2_evt(begin_time timestamp);
insert into app2_evt values('2016-05-18 11:30:00');


--事件表记录表
create table evt_tb_record(appname varchar(200),tbname varchar(200),colsql varchar(200
insert into evt_tb_record values('app1','app1_evt','start_time as evt_stime,''evt1'' as evt_type');
insert into evt_tb_record values('app2','app2_evt','begin_time as evt_stime,''evt2'' as evt_type');


--构建视图函数
CREATE OR REPLACE FUNCTION create_v_func()
RETURNS trigger AS
$BODY$
declare
rowinfo record;
vsql text;
dsql text;
index integer;
count integer;
begin
index := 0;
dsql := 'drop view if exists v_event';
execute dsql;
execute 'select count(*) from evt_tb_record' into count;


if count > 0 then
vsql := 'create view v_event as ';
for rowinfo in
select tbname,colsql from evt_tb_record
loop
vsql := vsql||' select '||rowinfo.colsql||' from '||rowinfo.tbname;
index := index + 1;
if index < count then
vsql := vsql||' union all ';
end if;
end loop;
execute vsql;
end if;


return NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


--触发器，当事件表变更时，重建视图
create trigger evt_trigger after insert or update or delete on evt_tb_record execute procedure create_v_func();


--新事件表加入(新app被安装)
create table app3_evt(start_time timestamp);
insert into app3_evt values('2016-05-18 10:30:00');


insert into evt_tb_record values('app3','app3_evt','start_time as evt_stime,''evt3'' as evt_type');


--查看视图

select * from v_event;//此时可以看到新app的事件表内容已经在视图中可以被查询到