[GENERAL] Rule system and unsucessful updates.

[Matthew Schumacher]

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster