From: George McQuade <josslad@yahoo.com>
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table conversion query...
Date: Wed, 20 Aug 2003 14:28:54 -0700 (PDT)
Message-ID: <20030820212854.13264.qmail@web41605.mail.yahoo.com> (raw)
In-Reply-To: <3F43D7E9.2000804@joeconway.com>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josslad@yahoo.com, mail@joeconway.com
Subject: Re: Table conversion query...
In-Reply-To: <20030820212854.13264.qmail@web41605.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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