pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable conversion query...
4+ messages / 2 participants
[nested] [flat]
* Table conversion query...
@ 2003-08-20 19:31 George McQuade <josslad@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: George McQuade @ 2003-08-20 19:31 UTC (permalink / raw)
To: pgsql-sql
Hello everyone,
I have a table that looks like:
date tran gl amt
08/20/03 1001 3010 -30.00
08/20/03 1001 1030 -300.00
08/20/03 1001 1060 +330.00
08/20/03 1002 ...next transaction
...
and I need to convert to:
date glcr gldb amt
08/20/03 1060 3010 30.00
08/20/03 1060 1030 300.00
in other words, the negative gl's go into gldb
and they make up the total for the positive gl.
is there a way to accomplish this in postgresql?
or should I implement it inside the java app?
thanks
george
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table conversion query...
@ 2003-08-20 20:19 Joe Conway <mail@joeconway.com>
parent: George McQuade <josslad@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joe Conway @ 2003-08-20 20:19 UTC (permalink / raw)
To: George McQuade <josslad@yahoo.com>; +Cc: pgsql-sql
George McQuade wrote:
> date tran gl amt
> 08/20/03 1001 3010 -30.00
> 08/20/03 1001 1030 -300.00
> 08/20/03 1001 1060 +330.00
> 08/20/03 1002 ...next transaction
> ...
> and I need to convert to:
>
> date glcr gldb amt
> 08/20/03 1060 3010 30.00
> 08/20/03 1060 1030 300.00
>
> in other words, the negative gl's go into gldb
> and they make up the total for the positive gl.
>
> is there a way to accomplish this in postgresql?
> or should I implement it inside the java app?
There's no simple way to do this in Postgres. You could do it with a
PL/pgSQL table function, or for better performance a C function. There
are a couple of questions yet to be answered though:
1) Can there ever be more than one credit account, e.g. -30, -300, +150,
+180?
2) What happens if sum(neg values) != sum(pos values)? Throw an error?
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table conversion query...
@ 2003-08-20 21:28 George McQuade <josslad@yahoo.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 4+ messages in thread
From: George McQuade @ 2003-08-20 21:28 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
--- Joe Conway <mail@joeconway.com> wrote:
> George McQuade wrote:
> > date tran gl amt
> > 08/20/03 1001 3010 -30.00
> > 08/20/03 1001 1030 -300.00
> > 08/20/03 1001 1060 +330.00
> > 08/20/03 1002 ...next transaction
> > ...
> > and I need to convert to:
> >
> > date glcr gldb amt
> > 08/20/03 1060 3010 30.00
> > 08/20/03 1060 1030 300.00
> >
> > in other words, the negative gl's go into gldb
> > and they make up the total for the positive gl.
> >
> > is there a way to accomplish this in postgresql?
> > or should I implement it inside the java app?
>
> There's no simple way to do this in Postgres. You
> could do it with a
> PL/pgSQL table function, or for better performance a
> C function.
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
> There are a couple of questions yet to be answered
> though:
> 1) Can there ever be more than one credit account,
> e.g. -30, -300, +150, +180?
No, so far all the examples I've seen involve a single
credit account.
> 2) What happens if sum(neg values) != sum(pos
> values)? Throw an error?
Yes, this would indicate a system out of balance
that requires external assistance.
Thanks for the help.
george
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table conversion query...
@ 2003-08-20 23:57 Joe Conway <mail@joeconway.com>
parent: George McQuade <josslad@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-08-20 23:57 UTC (permalink / raw)
To: George McQuade <josslad@yahoo.com>; +Cc: pgsql-sql
George McQuade wrote:
> Interesting, my C is gone a long time ago. Would the
> table function be fairly complex for someone who's
> never done one?
> I'm tempted by the java option, but initial jdbc speed
> tests don't look very promising (the avg file has
> 1/2 million records).
Well this is a fairly complex problem regardless of the language. You
need to be able to accumulate output rows for an arbitrary number of
different debit gls, flush them out whenever the transaction id changes,
and be sure that they reconcile with the credit.
I'm actually not sure you can do this in plpgsql in Postgres 7.3.x -- in
7.4 you could use arrays to accumulate the debit data. Here is a lightly
tested (no warranty, might not work correctly, use at your own risk etc,
etc ;-) ) plpgsql function which seems to work as you want it. Consider
it a starting point, but only if you can use 7.4beta:
create table gl (transdate date, tran int, gl int, amt numeric(9,2));
insert into gl values('08/20/03',1001,3010,-30.00);
insert into gl values('08/20/03',1001,1030,-300.00);
insert into gl values('08/20/03',1001,1060,330.00);
insert into gl values('08/21/03',1002,3010,-30.00);
insert into gl values('08/21/03',1002,1030,-200.00);
insert into gl values('08/21/03',1002,3010,-100.00);
insert into gl values('08/21/03',1002,1060,330.00);
create type reconcile_type as (transdate date, glcr int, gldb int, amt
numeric(9,2));
create or replace function reconcile(text) returns setof reconcile_type as '
declare
v_crit alias for $1;
v_sql text;
v_last_transdate date;
v_last_tran int := 0;
v_last_glcr int := 0;
v_last_glcr_amt numeric(9,2) := 0;
v_last_gldb int[] := ''{}'';
v_last_gldb_amt numeric(9,2)[] := ''{}'';
v_sum_debit numeric(9,2) := 0;
v_glcr_found bool := false;
rec record;
result reconcile_type%rowtype;
i int;
ub int;
begin
if v_crit is not null then
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl where ''
|| v_crit ||
'' group by transdate, tran, gl order by 2,4 desc,1,3'';
else
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl '' ||
''group by transdate, tran, gl order by 2,4 desc,1,3'';
end if;
for rec in execute v_sql loop
if rec.tran != v_last_tran then
-- starting a new tran
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
-- first pass for this tran -- it better be a credit
if rec.amt > 0 then
v_glcr_found := true;
else
RAISE EXCEPTION ''no credit found for transaction: %'', rec.tran;
end if;
v_last_tran := rec.tran;
v_last_transdate := rec.transdate;
v_last_glcr := rec.gl;
v_last_glcr_amt := rec.amt;
v_last_gldb := ''{}'';
v_last_gldb_amt := ''{}'';
v_sum_debit := 0;
else
-- not a new tran
if rec.amt > 0 then
-- if we have already visited the credit, and we see another,
cry foul
RAISE EXCEPTION ''Two credits found for transaction: %'', rec.tran;
else
-- otherwise accumulate the debit
v_last_gldb := v_last_gldb || rec.gl;
v_last_gldb_amt := v_last_gldb_amt || rec.amt;
v_sum_debit := v_sum_debit + rec.amt;
end if;
end if;
end loop;
-- need this to get the last (or only) trans
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
return;
end;
' language plpgsql;
regression=# select * from reconcile(null);
transdate | glcr | gldb | amt
------------+------+------+---------
2003-08-20 | 1060 | 3010 | -30.00
2003-08-20 | 1060 | 1030 | -300.00
2003-08-21 | 1060 | 3010 | -130.00
2003-08-21 | 1060 | 1030 | -200.00
(4 rows)
You could do similar a C function in 7.3.x. I'm not sure how you'd write
this in 7.3.x plpgsql though :(
HTH,
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-20 23:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-20 19:31 Table conversion query... George McQuade <josslad@yahoo.com>
2003-08-20 20:19 ` Joe Conway <mail@joeconway.com>
2003-08-20 21:28 ` George McQuade <josslad@yahoo.com>
2003-08-20 23:57 ` Joe Conway <mail@joeconway.com>
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