Thread: on insert when ... do instead update

Hello everybody!

I'm having problem with postgresql 8.3 (not sure if it is related to
this particular version). Suppose we have accounting system database
with the following rule (no other rules are there):

create or replace rule update_or_create_balance as
on insert to ledger when exists (select 1 from ledger where
account=new.account) do instead update ledger set debit=debit
+new.debit, credit=credit+new.credit, balance=balance+new.balance
where account=new.account;

What could be the reason of the following considering ledger table is
empty:

insert into ledger values(1,100,0,100);
select * from ledger;
           1 | 200.00 |   0.00 | 200.00
(1 row)

Looks like "debit+new.debit" and other expressions are processed as
"debit=new.debit+new.debit" etc. Am I missing something on rules
writing syntax?

Thanks in advance for any help.

Alex

askel <dummy666@mail.ru> writes:
> Hello everybody!
> I'm having problem with postgresql 8.3 (not sure if it is related to
> this particular version). Suppose we have accounting system database
> with the following rule (no other rules are there):

> create or replace rule update_or_create_balance as
> on insert to ledger when exists (select 1 from ledger where
> account=new.account) do instead update ledger set debit=debit
> +new.debit, credit=credit+new.credit, balance=balance+new.balance
> where account=new.account;

> What could be the reason of the following considering ledger table is
> empty:

> insert into ledger values(1,100,0,100);
> select * from ledger;
>            1 | 200.00 |   0.00 | 200.00
> (1 row)

You missed reading the bit in the docs where it says that a query added
by ON INSERT is done after the original query.  In the above example,
the WHEN clause will *always* succeed.

            regards, tom lane