Lists: | pgsql-sql |
---|
From: | Scott <lists(at)mail(dot)sael(dot)com(dot)au> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Insert Rule, Multiple Insert, Update Problem |
Date: | 2007-12-11 04:23:52 |
Message-ID: | 200712111423.52950.lists@mail.sael.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, Scott <lists(at)mail(dot)sael(dot)com(dot)au> |
Subject: | Re: Insert Rule, Multiple Insert, Update Problem |
Date: | 2007-12-11 04:50:02 |
Message-ID: | 152664.76353.qm@web31803.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
--- On Mon, 12/10/07, Scott <lists(at)mail(dot)sael(dot)com(dot)au> wrote:
> 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.
Yup, that is the limitation of rules. They are only useful if you issue statements that will only affect one tuple in a table at a time. If you want something different, you will have to use functions or triggers.