I'm having trouble getting the rule system to work on updates that do
not match the where clause. Perhaps I'm doing this wrong, but I can't
find any docs that explain this very clearly.
Here what I would like to do:
CREATE OR REPLACE RULE
insertAcctUpdate
AS ON UPDATE TO
accounting_tab
WHERE
NEW.type <> 'new'
AND
NOT EXISTS (
SELECT
sessionID
FROM
accounting_tab
WHERE
sessionID = NEW.sessionID
)
DO INSTEAD
INSERT INTO accounting_tab (
sessionID,
type
) values (
NEW.sessionID,
NEW.type
);
Basically when I get an update that doesn't have a row to update (due to
the sessionID missing) do an insert instead. For some reason it just
won't work, however the opposite (check for the insert and instead update):
CREATE OR REPLACE RULE
insertAcctUpdate
AS ON INSERT TO
accounting_tab
WHERE
NEW.type <> 'new'
AND
EXISTS (
SELECT
sessionID
FROM
accounting_tab
WHERE
sessionID = NEW.sessionID
)
DO INSTEAD
UPDATE
accounting_tab
set
(updates to columns)
where
type = NEW.type,
and
sessionID = NEW.sessionID;
Works just fine. The only thing I can think of is that the rule system
doesn't process the rule when it finds that the update modified 0 rows.
Anyone know why the first rule doesn't work but the second one does?
Thanks,
schu