Thread: Define update-rule on view

Hi there,
i got a table

xxtestobject(test_id serial,
       test_name varchar(254),
       test_active bool)

then i created a view

xxtest with
select * from xxtestobject where test_active=true;

i created a working rule for insert on the view.
now i want to implement a rule for an update.
the task is, that an update doesnt "update" the selected entry, it got
to set test_active to false, and insert a new row with the new data
and test_active settet to true.

i tried all the last view nights and now i'm not able to think any
more...

any ideas left ?

so many thanks in advance
Patrick

patriq@gmx.de (Patrick) writes:
> i created a working rule for insert on the view.
> now i want to implement a rule for an update.
> the task is, that an update doesnt "update" the selected entry, it got
> to set test_active to false, and insert a new row with the new data
> and test_active settet to true.

I don't believe you can do that with a rule --- you'll run into a
recursive-rule-application problem.  You could do it with a trigger,
but perhaps better is to reconsider your table design.  Seems like
it might be better to have two tables, one containing the active
entries and one containing the historical ones.

            regards, tom lane