Re: CREATE RULE on VIEW with INSERT after UPDATE does not work

Hi Tom,

thanks for your answer, I have also thought of combining
the statements, but my SQL-knowledge is too small for that.

I thought, the example with "mylog" would be better to
demonstrate the problem, but it's missing the point.
Below, if have added the code with my real problem.

What I want to do is a log of all starts and stops of validity.
So if a record is altered, I want the current one to be marked
by setting the stop-field to now() and another row to be added
to "mytable" with the same ID like the previous and start=now().

The Code below does only the mark-deleted-thing,
but does not insert a new record.

There is no unique-constraint on ID, I can add lines manually.

I also tried to swap the two lines in my Rule,
then a new row is inserted (good), but it is set
to end!=null by the second statement. (bad)

Maybe someone can give me a hint,
what's wrong with my code or my thinking?

Thanks, Peter

PS: Here's the NEW code with the uncooperative update-rule:

DROP VIEW myview;
DROP TABLE mytable;

CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS null;

CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);

CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;

CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD
(
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
INSERT INTO myview (id, proc, start, stop) VALUES (old.id, old.proc, now(), null);
);

-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');

-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;

-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;

-- The Process 2 is updated, but there is no entry in the log
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;

--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger