Rule system and unsucessful updates.

Поиск
Список
Период
Сортировка
От Matthew Schumacher
Тема Rule system and unsucessful updates.
Дата
Msg-id 43CB538A.4050002@aptalaska.net
обсуждение исходный текст
Ответы Re: Rule system and unsucessful updates.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Michael Schmidt"
Дата:
Сообщение: Re: visual query builder for Postgres?
Следующее
От: Zlatko Matić
Дата:
Сообщение: temporary tables, pgAdminIII