Skip site navigation (1) Skip section navigation (2)

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

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Hans-Peter Oeri <hp(at)oeri(dot)ch>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Implementation of a updateable, "temporal" view on data
Date: 2007-10-16 22:06:59
Message-ID: 905375.5251.qm@web31806.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- Hans-Peter Oeri <hp(at)oeri(dot)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.
          

In response to

Responses

pgsql-novice by date

Next:From: Hans-Peter OeriDate: 2007-10-17 13:14:54
Subject: Re: Implementation of a updateable, "temporal" view on data
Previous:From: Hans-Peter OeriDate: 2007-10-16 21:05:12
Subject: Implementation of a updateable, "temporal" view on data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group