Re: Novice PL/pgSQL question and example

James Long <pgsql-novice(at)museum(dot)rain(dot)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