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的事件表内容已经在视图中可以被查询到