Re: Table conversion query... - Mailing list 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_amtnumeric(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
in1..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
accumulatethe 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'';
endif;
 
    -- 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