Re: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT

On Thursday 11 March 2004 06:45, PostgreSQL Bugs List wrote:
> The following bug has been logged online:
>
> Bug reference: 1098
> Logged by: Tim Burgess
> Email address: tim(at)queens(dot)unimelb(dot)edu(dot)au

> Description: Multiple ON INSERT rules not applied properly in the
> case of INSERT...SELECT

> Rules:
> quips_transfer_from AS ON INSERT TO quips_transactions DO UPDATE
> users_quips SET balance = (users_quips.balance - new.amount) WHERE
> ((users_quips.username)::text = (new.user_from)::text)
> quips_transfer_to AS ON INSERT TO quips_transactions DO UPDATE
> users_quips SET balance = (users_quips.balance + new.amount) WHERE
> ((users_quips.username)::text = (new.user_to)::text)

> insert into quips_transactions select 'frontoffice_quips', member_username,
> 10, now(), 'Free Print Credit' from group_members where groupname =
> 'freshers_04';
>
> And all the transactions are added, however the rules do not execute
> properly. In our case, the quips_transfer_to rule worked fine - all the
> students had their balances credited. However, the quips_transfer_from
> rule was only applied once (the frontoffice_quips user had their balance
> lowered by $10, not $2180 as they should have).

The rule should only be applied once. The rule system basically rewrites the
insert you supply into two other insert queries (from/to). It will not
generate one query for each row in quips_transactions (which is what you
want). You probably need a trigger on the transactions table that issues
separate queries for each row inserted.

--
Richard Huxton
Archonet Ltd