Re: SQL for CREATE RULE

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
>