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
Actually, on my 8.3.9, it doesn't work:
pnwc=> SELECT G.A, TRUNC(90.0 / 7, 2) +
pnwc-> WHEN ROW_NUMBER() OVER (ORDER BY G.A) <= 100 * (90 - 7 * TRUNC(90.0 / 7, 2)) THEN
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?
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=#
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2010-02-08 23:04:58|
|Subject: Re: Incomplete pg_dump operation |
|Previous:||From: Tim Landscheidt||Date: 2010-02-08 21:17:38|
|Subject: Re: Novice PL/pgSQL question and example|