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@museum.rain.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice