LONG - Question on dealing w/ numerics

I have a function that is to create a Accounting JOURNAL entry.
The strange thing is the function works for simple entries such as:

Cash - Debit 100
A/R - Credit 100

But when I try to trick it or break it for testing purposes (IT DOES BREAK
WHEN IT SHOULDN'T) on a entry like this:

Cash - Debit 100
A/R - Credit 100
Cash - Credit 100
A/R - Debit 100
(Which should have a net affect of 0 on both accounts)

But here is the resulting balance on accounts,

Cash Debit Balance 200
A/R Credit Balance 200

Here is the function and I can't seem to figure out what is LOGICALLY
wrong and would produce these results.

create function
create_journal_entry_line(integer,integer,integer,numeric(20,2)) returns
INTEGER as '
DECLARE
eid ALIAS FOR $1;
aid ALIAS FOR $2;
ltype ALIAS FOR $3;
amount ALIAS FOR $4;
new_balance NUMERIC(20,2);
account_type RECORD;
account RECORD;
line RECORD;
BEGIN
select into account * from accounts where account_id = aid;

IF NOT FOUND THEN
return -1;
END IF;

IF account.account_active = ''f'' THEN
return -1;
END IF;

insert into journal_lines (entry_id,account_id,line_type,line_amount)
values (eid,aid,ltype,amount);
select into line * from journal_lines where entry_id = eid AND
account_id = aid AND ltype = ltype;
IF NOT FOUND THEN
return -1;
END IF;

select into account_type * from account_types where account_type_id =
account.account_type;

IF account_type.positive_account_balance_type = line.line_type THEN
new_balance := account.account_balance + amount;
ELSE
new_balance := account.account_balance - amount;
END IF;
UPDATE accounts SET account_balance = new_balance WHERE account_id =
account.account_id;
return line.entry_id;
END;' language 'plpgsql';

P.S. Line type represents 1 = Debit, 2 = Credit. The
positive_account_balance_type tells eithier if the account should have a
DEBIT or CREDIT balance (Represented the same as line type)

David,

> I have a function that is to create a Accounting JOURNAL entry.
> The strange thing is the function works for simple entries such as:

> Here is the function and I can't seem to figure out what is LOGICALLY
> wrong and would produce these results.

I'm not sure the problem is with the function. I think the problem is
with your program logic, as the funciton just inserts a *single*
journal line and updates the balance.

How do you insert the 4 entries required by a full double-entry
transfer as you described?

Also, how about posting a schema?

-Josh

David,

> Cash - Debit 100
> A/R - Credit 100
> Cash - Credit 100
> A/R - Debit 100
> (Which should have a net affect of 0 on both accounts)
>
> But here is the resulting balance on accounts,
>
> Cash Debit Balance 200
> A/R Credit Balance 200

Here may your problem, and it's in the schema: is "A/R" and account or
is "A/R Credit" an account? You're being unclear in your e-mail,
which makes me suspect that you're being vague in your code as well.

If I'm totally off the mark, then please post the four calls to your
function that produced the above mis-balance, and I can easily spot the
problem for you.

-Josh

"David Durst" <ddurst(at)larubber(dot)com> writes:
> insert into journal_lines (entry_id,account_id,line_type,line_amount)
> values (eid,aid,ltype,amount);
> select into line * from journal_lines where entry_id = eid AND
> account_id = aid AND ltype = ltype;

I bet that last should be line_type = ltype?

One thing you have to watch with plpgsql's SELECT INTO is that it
doesn't complain if the WHERE would match multiple rows. You get
one of the rows, and no indication that others would have matched.

regards, tom lane

"David Durst" <ddurst(at)larubber(dot)com> writes:
> But I still think your point about the function selecting more than
> one line is valid.

> The problem is, the journal_line_id is not created until the insert
> occurs and there is no other unique ident than the journal_line_id.

Well, my standard answer to that would be "your WHERE clause should
select on a primary key (which could be multiple columns)".

We're pretty constrained in changing the semantics of plpgsql, because
the raison d' etre of that language is to emulate Oracle's (TM) PL/SQL
(TM), warts and all.

(Now, if you can show us that PL/SQL behaves differently in that
situation, we'll definitely be willing to change plpgsql.)

regards, tom lane

> "David Durst" <ddurst(at)larubber(dot)com> writes:
>> insert into journal_lines
>> (entry_id,account_id,line_type,line_amount)
>> values (eid,aid,ltype,amount);
>> select into line * from journal_lines where entry_id = eid AND
>> account_id = aid AND ltype = ltype;
>
> I bet that last should be line_type = ltype?
Just to let you know, changing ltype to line_type fixed the problem.

But I still think your point about the function selecting more than
one line is valid.

The problem is, the journal_line_id is not created until the insert
occurs and there is no other unique ident than the journal_line_id.