> > Now, if you are doing double-entry bookkeeping this doesn't provide
> > enough consistency, IMO. You can't check inside the function to
> > ensure that the transaction is balanced.
>
> One option to consider is restricting final tables but making staging
> tables available.
I would implement this using triggers.
First, add a boolean "is_balanced" column to the transaction table,
along with the following trigger:
create or replace function check_txn_balance()
returns trigger
language plpgsql
as $$
declare
_amt numeric;
begin
if 'UPDATE' = TG_OP and new.is_balanced then
return null;
end if;
select sum(amt)
into _amt
from line_item
where txn_id = new.id;
if _amt <> 0 then
raise exception 'unbalanced transaction';
end if;
update txn
set is_balanced = true
where id = new.id;
return null;
end;
$$;
create constraint trigger check_txn_balance
after insert or update on txn
deferrable initially deferred
for each row execute procedure check_txn_balance();
Then, whenever we add, remove, or update a line item, unbalance the
parent transaction, which triggers the balance check:
create or replace function unbalance_txn()
returns trigger
language plpgsql
as $$
begin
if 'UPDATE' = TG_OP then
if (new.txn_id, new.amt) = (old.txn_id, old.amt) then
return null;
end if;
end if;
if TG_OP in ('INSERT', 'UPDATE') then
update txn
set is_balanced = false
where (id, is_balanced) = (new.txn_id, true);
end if;
if TG_OP in ('DELETE', 'UPDATE') then
update txn
set is_balanced = false
where (id, is_balanced) = (old.txn_id, true);
end if;
return null;
end;
$$;
create trigger unbalance_txn
after insert or delete or update on line_item
for each row execute procedure unbalance_txn();
At least, this seems to be a fairly efficient and foolproof way to do it
to me.
Karl Nack
Futurity, Inc
5121 N Ravenswood Ave
Chicago, IL 60640
773-506-2007