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

Implementation of a updateable, "temporal" view on data

From: Hans-Peter Oeri <hp(at)oeri(dot)ch>
To: pgsql-novice(at)postgresql(dot)org
Subject: Implementation of a updateable, "temporal" view on data
Date: 2007-10-16 21:05:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

OK, I'm new to pgsql but would like to implement a updateable,
"temporal" view. By "temporal" I mean that the table has fields for
start and end time of the row's validity. The view on the table should
only display "current" rows.

An "update" on one of those current rows actually consists of two
actions: a) update the old row, setting its "stop" field to now(). b)
insert a new row with updated values, "start"-ing now().

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 AND table.start=old.start
is "translated" to:
... WHERE AND table.start=table.start

OK, I fallback to instead-triggers; but triggers on views are not
supported. As applications also need the "archive" access to current and
past row versions, I cannot "trigger" the main table.

Well, second fallback. I try a (second) dummy table with triggers that
update the main table. For SELECT access I try to create a rule -
redirecting the query to current rows of the main table; However, select
rules are only allowed for "true" views...

As such, I'm stuck (with version 8.2.4, if necessary). Could anyone give
me a clue if/how it's possible to implement such a "temporal" view on
data in pgsql?

Any help or link greatly appreciated


pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-10-16 22:06:59
Subject: Re: Implementation of a updateable, "temporal" view on data
Previous:From: Oliver ElphickDate: 2007-10-16 17:14:49
Subject: Re: Compiling Functions in Postgresql

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