SQL for CREATE RULE

Поиск
Список
Период
Сортировка
От Bryan Buchanan
Тема SQL for CREATE RULE
Дата
Msg-id 3B529380.92AF8640@webbtide.com.au
обсуждение исходный текст
Ответы Re: SQL for CREATE RULE
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Thomas Lockhart
Дата:
Сообщение: Re: BYTEA data type
Следующее
От: Vince Vielhaber
Дата:
Сообщение: Re: BYTEA data type