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