pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rule question
3+ messages / 3 participants
[nested] [flat]

* Rule question
@ 1999-10-09 17:07 nickyk <nickyk@gmx.net>
  0 siblings, 0 replies; 3+ messages in thread

From: nickyk @ 1999-10-09 17:07 UTC (permalink / raw)
  To: pgsql-sql

Hi everybody

I have the following situation:
there are 3 tables that have relatiosn between them and I want when
insert or update to insert/update all of them - so I creale a rules
unfortnately this tables have too many columns and when I create a view
over them the definition of the rule is over 8k and can not be saved
into the database

can you be so kind to tell me some workaround to avoid this situation?

regards

Nickolay Kolev




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

* Rule Question
@ 1999-12-19 21:40 Mitch Vincent <mitch@venux.net>
  1999-12-19 22:52 ` Re: [SQL] Rule Question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Mitch Vincent @ 1999-12-19 21:40 UTC (permalink / raw)
  To: pgsql-sql

Boy, I'm just full of questions this weekend. On with another one..

The overall objective here is to have the field "total" on an invoice
calculated every time the table is updated, so it's always up to date. I
could do this programatically but there are so many ways that this can
change, it would be great if Postgre just did it for me every time :-)

From what I read about rules, this is possible so what I did was this :


Created this function to do the totalling:

 create function total_up(int4) returns float8 as 'select fee_membership +
fee_logins + fee_convention + fee_prints+ fee_hotlines + fee_postage +
fee_ups + fee_late + fee_other1 + fee_other2 + fee_other3 + fee_pastdue from
invoice where invoice_number = $1;' language 'sql';

And this rule to call the function and update the invoice that has been
updated, inserting the new total.  :

 create rule total_invoice as on update to invoice where
invoice_number=NEW.invoice_number DO update invoice set total =
total_up(NEW.invoice_number) where invoice_number=NEW.invoice_number;

However I get this when I do an update on an invoice:

PostgreSQL Error: 1 (ERROR: query rewritten 10 times, may contain cycles )
Session halted.

Some kind of loop? Well, I didn't see any kind of loop or error but that is
probably due to my little understanding of rules..

Thanks yet again!

-Mitch





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

* Re: [SQL] Rule Question
  1999-12-19 21:40 Rule Question Mitch Vincent <mitch@venux.net>
@ 1999-12-19 22:52 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-12-19 22:52 UTC (permalink / raw)
  To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql

"Mitch Vincent" <mitch@venux.net> writes:
> Created this function to do the totalling:

>  create function total_up(int4) returns float8 as 'select fee_membership +
> fee_logins + fee_convention + fee_prints+ fee_hotlines + fee_postage +
> fee_ups + fee_late + fee_other1 + fee_other2 + fee_other3 + fee_pastdue from
> invoice where invoice_number = $1;' language 'sql';

> And this rule to call the function and update the invoice that has been
> updated, inserting the new total.  :

>  create rule total_invoice as on update to invoice where
> invoice_number=NEW.invoice_number DO update invoice set total =
> total_up(NEW.invoice_number) where invoice_number=NEW.invoice_number;

> However I get this when I do an update on an invoice:

> PostgreSQL Error: 1 (ERROR: query rewritten 10 times, may contain cycles )

Not sure, but I think the problem is that the rule is recursively
applied to itself --- it's defined to fire on any UPDATE to the invoice
table, and inside the rule you ask for another UPDATE to invoice, so
you got trouble.  A rule has to reduce the given case to something
different.

I'd be inclined to do this with a trigger instead of a rule.  To do it
with a trigger, you go ahead and define the total column as a real
column in the database, but then you put in a trigger that calculates
the correct value from the rest of the tuple whenever a tuple is
inserted or updated, overriding whatever the old value may have been
(or whatever the application tried to supply!).  It'd look something
like this if you use plpgsql:

CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
BEGIN
  NEW.total := NEW.fee_membership + NEW.fee_logins + ...;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER invoice_total_trigger BEFORE INSERT OR UPDATE ON invoice
FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();

Another way is to leave the total column out of the underlying table,
and define a VIEW that includes all the underlying columns plus the
total, computing the total on-the-fly:
	SELECT *, fee_membership + fee_logins + ... FROM invoice
This'd probably be better if you anticipate many more updates than
queries, but it'd be a loser if many more queries than updates.
Details left as an exercise for the student...

			regards, tom lane



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


end of thread, other threads:[~1999-12-19 22:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-09 17:07 Rule question nickyk <nickyk@gmx.net>
1999-12-19 21:40 Rule Question Mitch Vincent <mitch@venux.net>
1999-12-19 22:52 ` Re: [SQL] Rule Question Tom Lane <tgl@sss.pgh.pa.us>

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