Re: Novice PL/pgSQL question and example

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