Chart of Accounts - Mailing list pgsql-general

Dear All,

I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently.  I am trying
toimplement an "intelligent" Chart of Accounts for an accounting program.  The following is long-winded but please bear
withme: 

I have a table coa (chart of accounts) with the following schema

  CREATE TABLE coa(
    coa_id serial not null,
    parent_id int not null default 0,
    account_name text not null,
    amt money default 0,
    primary key(coa_id)
  );

After populating the database with basic accounts it resembles this (the hierarchy is mine):

  coa_id, parent_id, account_name,          amt
  0,        -1,      'Chart of Accounts',    0.00
  1,         0,         'Assets',            0.00
  5,         1,           'Fixed Assets',    0.00
  6,         5,             'Motor Van',     0.00
 --truncated ---
  2,         0,       'Liabilities',         0.00
  3,         0,       'Income',              0.00
  4,         0,       'Expenses',            0.00

So far, so good.  I would like it so that if the amt of a a child account changes, the parent account is updated, if a
childaccount is deleted, the amount is reduced off of the parent account etc. 

I have managed to achieve this using the following trigger functions:

CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS
$body$
begin
    update coa set amt = amt - old.amt where coa_id = old.parent_id;
    return old;
end;
$body$
LANGUAGE 'plpgsql'

------------------

CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS
$body$
begin
    UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id;
    return new;
end;
$body$
LANGUAGE 'plpgsql'

------------

CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS
$body$
begin
    IF new.parent_id = old.parent_id THEN
        UPDATE coa SET amt = amt + (new.amt - old.amt)
                WHERE coa_id = new.parent_id;
    ELSE
        UPDATE coa SET amt = amt - old.amt
                   WHERE parent_id = old.parent_id;
        UPDATE coa SET amt = amt + new.amt
                   WHERE parent_id = new.parent_id;
    END IF;
    RETURN new;
end;
$body$
LANGUAGE 'plpgsql'

------------

These have been bound to the respective ROW before triggers.  And they work as expected upto a certain extent. eg
assigninga value to 'Motor Van' updates the relevant parent accounts: 

  UPDATE coa SET amt = 4000 WHERE coa_id = 6;

The problem comes about when one wants to change the parent account for a sub account eg, assuming in the example above
that'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat interesting
becausethe amt for all related accounts are reset to unpredictible values, AND the parent_id does not change anyway. 

The problem lies squarely in the function coa_upd_amt().

Any ideas.

Thank you.