"James F. Hranicky" <jfh@cise.ufl.edu> writes:
> What I'd really like to do is this:
> create rule homes_update as on update to homes where OLD.fspath !=
> NEW.fspath do instead
> (
> update homes_table set record_expired = now() where fsname
> = OLD.fsname and record_expired is null;
> insert into homes_table values (NEW.fsname, NEW.fspath);
> );
> However, when I do that, it seems the insert fails to execute, or fails
> silently in some way.
The reason it doesn't work is that the INSERT is done conditionally on
the existence of view rows satisfying the rule's WHERE and the original
query's WHERE. For example, given
update homes set fspath = 'mach1:/exp/h02' where fsname = 'h02';
the second part of the rule expands to something like
INSERT INTO homes_table
SELECT
'h02', -- substituted for NEW.fsname
'mach1:/exp/h02' -- substituted for NEW.fspath
FROM homes OLD
WHERE OLD.fspath != 'mach1:/exp/h02' -- rule WHERE
AND OLD.fsname = 'h02'; -- original WHERE
The trouble is that after the UPDATE done by the first part of the
rule, there are no view rows satisfying the WHERE conditions (you've
set record_expired to non-null in all the homes_table rows that might
have matched).
Basically, rules are macros that get substituted into the given query.
If you do anything that's even slightly self-referential then you are
likely to get confused. It's a lot easier to wrap your mind around a
trigger --- the extra notational complexity of having to write a trigger
function is more than made up for by conceptual simplicity.
My suggestion is to do this with triggers and a separate history table.
Say,
homes (fsname primary key, fspath, record_added default now());
homes_log (fsname, fspath, record_added, record_expired);
and a trigger that does an insert into homes_log on any update or delete
of homes.
regards, tom lane