Обсуждение: Rule Question

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

"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