Hi James, There is some my publications about SART AML System based on banking General Ledger (OLAP Data Warehouse and Chart of Accounts as dimension with 60 000+ items) - may be helpful. http://www.analyticsql.org/documentation.html http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf Regards, Blazej Oleszkiewicz 2008/10/12 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
