Re: Implementation of a updateable, "temporal" view on data

Hi!

Richard Broersma Jr wrote:
> This way your current record stays current and you simply insert
> history records.
Sometimes there is an obvious easy way ;) Thanks a lot!

However, I had to adapt the solution a little:
In order to avoid (undeferrable) primary key conflicts, I have to
*first* move the start time 'out of the way', then insert the new row
(with corrected start time).

For the archives:

UPDATE table SET start = start + '00:00:00.10'::interval
  WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;
  -- 2037-12-XX being my definition of  'eternity'

INSERT INTO table(id,start,stop,val,...)
  VALUES (old.id, old.start - '00:00:00.10'::interval, now(), old.val ...);

UPDATE table SET start = now(), val=...
  WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;

(The short time that (errorously) two periods 'overlap' has to be taken
into account for checks!)


HPO