Re: Novice PL/pgSQL question and example

James Long <pgsql-novice@museum.rain.com> wrote:

> 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.

I wouldn't use that terminology if I would present it to
someone in accounting, but essentially: Yes.

> 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?
> [...]

The ROW_NUMBER() construct is a windowing function intro-
duced in 8.4. You can either use one of the workarounds
listed in
<URI:http://www.postgresonline.com/journal/index.php?/archives/79-Simulating-Row-Number-in-PostgreSQL-Pre-8.4.html>
or, if you prefer an imperative approach, loop over the rows
to update and increment your own counter.

Tim