Re: [SQL] Rule Question

"Mitch Vincent" <mitch(at)venux(dot)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