> AFAICS, all you need to do is swap the ordering of those two operations.
>
> It might help to understand that what you write as an INSERT/VALUES is
> really more like INSERT ... SELECT ... FROM myview WHERE ..., the WHERE
> condition being the same as was given in the "UPDATE myview" command
> that the rule rewrites. As soon as you change the stop value in the
> UPDATE mytable, the SELECT from the view will find nothing.
>
> regards, tom lane
Ok, but swapping the two statements leads to another problem.
When executing these three statements,
I want the beta-line to have stop=null.
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
UPDATE myview SET proc='beta' WHERE id = 2;
But I always get this result, because the id is 2 in both rows:
id | proc | start | stop
----+-------+--------------------------+--------------------------
1 | alpha | 2007-08-11 02:32:04.7866 |
2 | omega | 2007-08-11 02:32:04.793 | 2007-08-11 02:32:04.8127
2 | beta | 2007-08-11 02:32:04.8127 | 2007-08-11 02:32:04.8127
So maybe, I need another condition in the update-statement,
but I don't know, which one to use.
Thanks in advance, Peter
PS: New Code with swapped lines:
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
(
INSERT INTO mytable (id, proc, start, stop) VALUES (old.id, new.proc, now(),
null);
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null; -- AND
<some-condition>;
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;
SELECT * FROM mytable ORDER BY id,start;
SELECT * FROM myview ORDER BY id,start;
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
