Chart of Accounts

[James Hitz <jam_hit@xxxxxxxxx>]

Dear All,

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

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 child account 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 assigning a 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 because the 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.




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general