Implementation of a updateable, "temporal" view on data
От | Hans-Peter Oeri |
---|---|
Тема | Implementation of a updateable, "temporal" view on data |
Дата | |
Msg-id | 47152788.20105@oeri.ch обсуждение исходный текст |
Ответы |
Re: Implementation of a updateable, "temporal" view on data
Re: Implementation of a updateable, "temporal" view on data |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: