pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 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 22:52 Tom Lane <tgl@sss.pgh.pa.us> parent: Mitch Vincent <mitch@venux.net> 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