drop schema if exists refgeo cascade;
create schema refgeo;

drop table if exists refgeo.hzone cascade;
create table refgeo.hzone (
        hid serial                              -- historical id
        , id serial                             -- normal id
        , name varchar                          -- zone name
        , funcid varchar                        -- functional identifier
        , geom varchar  -- varchar for the zone
        , sdate timestamp       default current_timestamp       -- start date : data is valid from that date on
        , edate timestamp       default null    -- end date : data is valid until that date
);

-- the view to current data
create or replace view 
        refgeo.zone as 
select
        id
        , name
        , funcid
        , geom
from
        refgeo.hzone
where
        edate is null;

-- make this view updatable
-- this mechanism will be almost automatic with PG >= 9.3
-- insert
create rule refgeo_zone_ins as
on insert to refgeo.zone do instead
insert into refgeo.hzone (id, name, funcid, geom) values (NEW.id, NEW.name, 
NEW.funcid, NEW.geom);
-- update
create rule refgeo_zone_up as
on update to refgeo.zone do instead
(
update refgeo.hzone set edate = current_timestamp where id = OLD.id and edate 
is null;
insert into refgeo.hzone (id, name, funcid, geom) values (OLD.id, NEW.name, 
NEW.funcid, NEW.geom);
);
;
-- datsup current value instead of delete
create rule refgeo_zone_del as
on delete to refgeo.zone do instead
update refgeo.hzone set edate = current_timestamp where id = OLD.id and edate 
is null;

/* test it */
truncate refgeo.hzone;

insert into 
        refgeo.hzone (id, name, funcid, geom, sdate, edate)
select 
        n as id
        , 'Point ' || n::text as name
        , n as funcid
        , ''
        , current_timestamp - interval '1 month' as sdate
        , current_timestamp as edate
from 
        generate_series(1, 1000) as n
union
select 
        n as id
        , 'Point ' || n::text as name   
        , n + 1000 as funcid
        , ''
        , current_timestamp as sdate
        , null as edate
from 
        generate_series(1, 1000) as n;

-- get all data
select * from refgeo.hzone;

-- get current data
select * from refgeo.zone;

-- insert new data
insert into refgeo.zone (id, name, funcid, geom) values (3500, 'Point 3500', 
3500, 'POINT(33 33)'::varchar);

-- see new data
select * from refgeo.zone order by id desc limit 10;
select * from refgeo.hzone order by id desc limit 10;

-- update our point
update refgeo.zone set geom = 'POINT(42 42)'::varchar where id = 3500;
select * from refgeo.zone order by id desc limit 10;
select * from refgeo.hzone order by id desc limit 10;