From: | James Long <pgsql-novice(at)museum(dot)rain(dot)com> |
---|---|
To: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Novice PL/pgSQL question and example |
Date: | 2010-02-08 21:44:09 |
Message-ID: | 20100208214409.GC47010@ns.umpquanet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
An interesting approach, one of which I wouldn't have conceived.
I am trying to understand how it works. You calculate a first
approximation based on discarding the fractional penny, and
then adjust that by adding on a whole penny in some cases, so
that the sum of the shares matches the original amount that
was divided.
Actually, on my 8.3.9, it doesn't work:
pnwc=> SELECT G.A, TRUNC(90.0 / 7, 2) +
pnwc-> CASE
pnwc-> WHEN ROW_NUMBER() OVER (ORDER BY G.A) <= 100 * (90 - 7 * TRUNC(90.0 / 7, 2)) THEN
pnwc-> 0.01
pnwc-> ELSE
pnwc-> 0.00
pnwc-> END
pnwc-> FROM generate_series(1, 7) AS G(A);
ERROR: syntax error at or near "OVER"
LINE 3: WHEN ROW_NUMBER() OVER (ORDER BY G.A) <= 100 ...
^
What version are you running, or what am I doing wrong that
prevents me from reproducing your results?
Thanks!
Jim
On Mon, Feb 08, 2010 at 09:17:38PM +0000, Tim Landscheidt wrote:
> James Long <pgsql-novice(at)museum(dot)rain(dot)com> wrote:
>
> > [...]
> > Is that as good as this can get, or is there a simpler way, more
> > along the lines of the first version?
>
> I'm not certain that I get the gist of your share_costs ()
> function, but why not just something functional along the
> lines of:
>
> | tim=# SELECT G.A, TRUNC(90.0 / 7, 2) +
> | tim-# CASE
> | tim-# WHEN ROW_NUMBER() OVER (ORDER BY G.A) <= 100 * (90 - 7 * TRUNC(90.0 / 7, 2)) THEN
> | tim-# 0.01
> | tim-# ELSE
> | tim-# 0.00
> | tim-# END
> | tim-# FROM generate_series(1, 7) AS G(A);
> | a | ?column?
> | ---+----------
> | 1 | 12.86
> | 2 | 12.86
> | 3 | 12.86
> | 4 | 12.86
> | 5 | 12.86
> | 6 | 12.85
> | 7 | 12.85
> | (7 Zeilen)
>
> | tim=#
>
> Tim
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-02-08 23:04:58 | Re: Incomplete pg_dump operation |
Previous Message | Tim Landscheidt | 2010-02-08 21:17:38 | Re: Novice PL/pgSQL question and example |