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: m3ljf38cfb.fsf@passepartout.tim-landscheidt.de (view raw or flat)
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
<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

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-2014 The PostgreSQL Global Development Group