pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLONG - Question on dealing w/ numerics
6+ messages / 3 participants
[nested] [flat]
* LONG - Question on dealing w/ numerics
@ 2003-01-29 00:54 David Durst <ddurst@larubber.com>
0 siblings, 3 replies; 6+ messages in thread
From: David Durst @ 2003-01-29 00:54 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: LONG - Question on dealing w/ numerics
@ 2003-01-29 02:50 Josh Berkus <josh@agliodbs.com>
parent: David Durst <ddurst@larubber.com>
2 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-01-29 02:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: LONG - Question on dealing w/ numerics
@ 2003-01-29 02:54 Josh Berkus <josh@agliodbs.com>
parent: David Durst <ddurst@larubber.com>
2 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-01-29 02:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: LONG - Question on dealing w/ numerics
@ 2003-01-29 04:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Durst <ddurst@larubber.com>
2 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-01-29 04:08 UTC (permalink / raw)
To: ddurst@larubber.com; +Cc: pgsql-sql
"David Durst" <ddurst@larubber.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: LONG - Question on dealing w/ numerics
@ 2003-01-29 09:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Durst <ddurst@larubber.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-01-29 09:01 UTC (permalink / raw)
To: ddurst@larubber.com; +Cc: pgsql-sql
"David Durst" <ddurst@larubber.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: LONG - Question on dealing w/ numerics
@ 2003-01-29 09:17 David Durst <ddurst@larubber.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David Durst @ 2003-01-29 09:17 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
> "David Durst" <ddurst@larubber.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-01-29 09:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-29 00:54 LONG - Question on dealing w/ numerics David Durst <ddurst@larubber.com>
2003-01-29 02:50 ` Josh Berkus <josh@agliodbs.com>
2003-01-29 02:54 ` Josh Berkus <josh@agliodbs.com>
2003-01-29 04:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-29 09:17 ` David Durst <ddurst@larubber.com>
2003-01-29 09:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox