pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Insert Rule, Multiple Insert, Update Problem
2+ messages / 2 participants
[nested] [flat]

* Insert Rule, Multiple Insert, Update Problem
@ 2007-12-11 04:23 Scott <lists@mail.sael.com.au>
  2007-12-11 04:50 ` Re: Insert Rule, Multiple Insert, Update Problem Richard Broersma Jr <rabroersma@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Scott @ 2007-12-11 04:23 UTC (permalink / raw)
  To: 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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Insert Rule, Multiple Insert, Update Problem
  2007-12-11 04:23 Insert Rule, Multiple Insert, Update Problem Scott <lists@mail.sael.com.au>
@ 2007-12-11 04:50 ` Richard Broersma Jr <rabroersma@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Broersma Jr @ 2007-12-11 04:50 UTC (permalink / raw)
  To: pgsql-sql@postgresql.org, Scott <lists@mail.sael.com.au>

--- On Mon, 12/10/07, Scott <lists@mail.sael.com.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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-12-11 04:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-11 04:23 Insert Rule, Multiple Insert, Update Problem Scott <lists@mail.sael.com.au>
2007-12-11 04:50 ` Richard Broersma Jr <rabroersma@yahoo.com>

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