Skip site navigation (1) Skip section navigation (2)

Re: Novice PL/pgSQL question and example

From: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Novice PL/pgSQL question and example
Date: 2010-02-09 00:45:12
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
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
or, if you prefer an imperative approach, loop over the rows
to update and increment your own counter.


In response to

pgsql-novice by date

Next:From: Irvin GuyettDate: 2010-02-09 02:10:57
Subject: How to continue Installation if stalled? and using Joomla?
Previous:From: Tom LaneDate: 2010-02-08 23:04:58
Subject: Re: Incomplete pg_dump operation

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group