Rule system and unsucessful updates. - Mailing list pgsql-general

From Matthew Schumacher
Subject Rule system and unsucessful updates.
Date
Msg-id 43CB538A.4050002@aptalaska.net
Whole thread Raw
Responses Re: Rule system and unsucessful updates.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "Michael Schmidt"
Date:
Subject: Re: visual query builder for Postgres?
Next
From: Zlatko Matić
Date:
Subject: temporary tables, pgAdminIII