Re: CREATE RULE on VIEW with INSERT after UPDATE does not work - Mailing list pgsql-general

> 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