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

Re: Novice PL/pgSQL question and example

From: James Long <pgsql-novice(at)museum(dot)rain(dot)com>
To: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Novice PL/pgSQL question and example
Date: 2010-02-08 21:44:09
Message-ID: 20100208214409.GC47010@ns.umpquanet.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)museum(dot)rain(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-02-08 23:04:58
Subject: Re: Incomplete pg_dump operation
Previous:From: Tim LandscheidtDate: 2010-02-08 21:17:38
Subject: Re: Novice PL/pgSQL question and example

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