Thread: SQL for CREATE RULE
Hi, Would anyone know if it's possible to define a rule to do the folowing. I have two tables: journal_master (serial #, account, date, debit $, credit $) journal_summary (account, date, debit $, credit $) each time a transaction occurs, a new journal_master row is added, and the cumulative amounts added to the summary table. I can have a rule like CREATE RULE js_update AS ON INSERT TO journal_master DO UPDATE journal_summary set debit = debit + new.debit, set credit = credit + new.credit where account = new.account and date = new.date; My problem is that if the journal_summary row doesn't exist, I really want to an INSERT. In pseudo code, something like: CREATE RULE js_update AS ON INSERT TO journal_master DO if journal summary row exists for this date and account then UPDATE journal_summary set debit = debit + new.debit, set credit = credit + new.credit where account = new.account and date = new.date else INSERT INTO journal_summary (account, date, debit, credit) VALUES (new.account, new.date, new.debit, new.credit) end Is it possible ? Thanks, Bryan
Bryan Buchanan wrote: > I have two tables: > > journal_master (serial #, account, date, debit $, credit $) > journal_summary (account, date, debit $, credit $) > > each time a transaction occurs, a new journal_master row is > added, and the cumulative amounts added to the summary table. You don't need a RULE but a TRIGGER. Check out PostgreSQL's documentation. Yes, it's possible and it's similar to your solution. -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
On Mon, 16 Jul 2001, Bryan Buchanan wrote: > Hi, > > Would anyone know if it's possible to define a rule to do the folowing. Why use a rule and not a trigger? > I have two tables: > > journal_master (serial #, account, date, debit $, credit $) > journal_summary (account, date, debit $, credit $) > > each time a transaction occurs, a new journal_master row is > added, and the cumulative amounts added to the summary table. > > I can have a rule like > > CREATE RULE js_update AS > ON INSERT TO journal_master > DO > UPDATE journal_summary set debit = debit + new.debit, > set credit = credit + new.credit where account = new.account and > date = new.date; > > My problem is that if the journal_summary row doesn't exist, I > really want to an INSERT. In pseudo code, something like: > > CREATE RULE js_update AS > ON INSERT TO journal_master > DO > if journal summary row exists for this date and account then > UPDATE journal_summary set debit = debit + new.debit, > set credit = credit + new.credit where account = new.account and > date = new.date > else > INSERT INTO journal_summary (account, date, debit, credit) > VALUES (new.account, new.date, new.debit, new.credit) > end > > Is it possible ? I'd do a trigger in plpgsql. The procedure body would more or less look like: table_row RECORD; SELECT INTO table_row * from journal_summary where account=NEW.account and date=NEW.date; IF NOT FOUND THEN //do an INSERT to journal_summary ELSE //do the UPDATE to journal_summary END IF; Read the online doc for the plpgsql description. cheers, thalis > > Thanks, > > Bryan > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >