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
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: |