Rule system and unsucessful updates.

From: Matthew Schumacher <matt(dot)s(at)aptalaska(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Rule system and unsucessful updates.
Date: 2006-01-16 08:04:26
Message-ID: 43CB538A.4050002@aptalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mikael Carneholm 2006-01-16 12:49:20 Re: Plans for 8.2?
Previous Message Mavinakuli, Prasanna (STSD) 2006-01-16 06:40:59 Re: Strange error while executing query from front end: