From: Scott <lists@mail.sael.com.au>
To: pgsql-sql@postgresql.org
Subject: Insert Rule, Multiple Insert, Update Problem
Date: Tue, 11 Dec 2007 14:23:52 +1000
Message-ID: <200712111423.52950.lists@mail.sael.com.au> (raw)
Hi,
I am having trouble with an insert rule that also does an update. It works
fine for a single insert but multiple inserts in a single statement don't
behave as I expected.
An outline of the problem is:
A table "trans" has a "client_id" and a transaction amount called "points".
Another table "clienst" also holds the "client_id" and an accumulated
amount "total_points". I create a rule for the trans table:
create rule trans_insert as on insert to trans do also
update
clients
set
total_points = total_points + NEW.points;
where
client_id = NEW.client_id;
This works fine for a single insert but if I dump multiple transactions for
multiple clients in the trans table only the first transaction for each
client is accounted for in clients.total_points.
I am just about to try and implement this as a trigger but it is driving me
crazy as to why this won't work as a rule. If I put something like the
following:
insert into foo values(NEW.points);
Then every transaction points value is copied to foo.
Any help is very appreciated.
Thanks,
Scott.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@mail.sael.com.au
Subject: Re: Insert Rule, Multiple Insert, Update Problem
In-Reply-To: <200712111423.52950.lists@mail.sael.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox