Обсуждение: 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
>