| From: | Bryan Buchanan <bryanb(at)webbtide(dot)com(dot)au> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | SQL for CREATE RULE |
| Date: | 2001-07-16 07:10:56 |
| Message-ID: | 3B529380.92AF8640@webbtide.com.au |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alessio Bragadini | 2001-07-16 09:32:36 | Re: Translators wanted |
| Previous Message | Oleg Bartunov | 2001-07-16 06:27:32 | Re: [HACKERS] Translators wanted |