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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
