Re: Implementation of a updateable, "temporal" view on data - Mailing list pgsql-novice

--- Hans-Peter Oeri <hp@oeri.ch> wrote:
> I tried to implement this using pgsql rules on the view - but I seem
> unable to restrict step a to only THE old row:
> UPDATE table SET stop=now() WHERE table.id=old.id AND table.start=old.start
> is "translated" to:
> ... WHERE table.id=table.id AND table.start=table.start
> ;(
>


I would do this a little differently.

INSERT INTO table ( starttime, endtime, val1, val(...) )
VALUES( old.endtime, now(), old.val1, old.val(...) );

UPDATE table
SET starttime = now(),
    val1 = new.val1,
    val... = new.val(...)
WHERE now() between starttime AND endtime;


This way your current record stays current and you simply insert history records.

Regards,
Richard Broersma Jr.