Thread: Novice PL/pgSQL question and example
At least, I hope this is a Novice question. It sure makes me
feel like one! :) Please cc: me directly on replies, as I might
miss a reply that goes only to the list.
I'm running PostGreSQL 8.3.9 on FreeBSD 6.4-STABLE, but I doubt
this is platform-dependent.
I'm trying to solve what I'm sure is a common problem in the
accounting world. A total cost C has to be split across N
different accounts, and C is not necessarily divisible by N.
Shares with fractions of pennies are not allowable, and to make
the accounting balance, the sum of all the shares has to sum to
exactly match the total cost C.
It's like splitting a $90 lunch check between 7 people.
This PHP code shows the math I'm using, and works great. For
clarity to those not familiar with PHP, the &$error in the
calc_share function declaration means that parameter $error is
passed by reference, not by value. This is necessary because the
calc_share function has to keep track of the cumulative error
from one share to the next that arises from taking a real number
and rounding it to two decimal places. Eventually, the
cumulative error will be sufficient to bump a share up or down in
value by one penny. I'm using OUT parameters in the PL/pgSQL
version to achieve this result.
<?php
function calc_share( $amount, $shares, &$error )
{
$share = $amount / $shares;
$result = round( $share + $error, 2 );
$error += $share - $result;
return $result;
}
$amount = 90;
$shares = 7;
$error_term = 0;
$test_sum = 0;
for ( $i = 0; $i < $shares; ) {
++$i;
$one_share = calc_share( $amount, $shares, $error_term );
print "i = " . $i . " " . $one_share . "\n";
$test_sum = $test_sum + $one_share;
}
print "sum = " . $test_sum . "\n";
?>
Here is my attempt to implement this in PL/PGSQL.
The commented-out UPDATE query is how I would like to use this
function in a real-world application (given that there are exactly 7
rows in my table WHERE reference = 'SHARE').
But for now, I'm using the FOR loop and the RAISE NOTICE just to
try to see what's going on, and whether my algorithm is working
correctly.
It's not.
--- begin test.sql
CREATE OR REPLACE FUNCTION calc_share( cost REAL, N_shares INTEGER,
OUT error_term REAL, OUT result REAL ) AS $$
-- When called N consecutive times with identical values of
-- COST and N_SHARES, this routine will calculate N shares of a
-- value COST and keep track of the error term, so that some shares
-- may be one penny higher than other shares, but the sum of all the
-- shares will always match the total COST.
-- The caller must set error_term to 0 before the first call.
DECLARE
one_share REAL;
result REAL;
BEGIN
one_share := cost / N_shares;
result := ROUND( one_share + error_term, 2 );
error_term := error_term + one_share - result;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION share_costs() RETURNS VOID AS $$
DECLARE
error_term REAL;
shares INTEGER;
i INTEGER;
cost REAL;
one_share REAL;
BEGIN
error_term := 0;
cost := 90;
shares := 7;
-- update my_table set amount = calc_share( cost, shares, error_term ) where reference = 'SHARE';
error_term := 0;
FOR i IN 1 .. shares LOOP
PERFORM calc_share( cost, shares, error_term, one_share );
RAISE NOTICE 'i = %, share = %', i, one_share;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
--- end test.sql
I'm trying to invoke this code thusly:
$ psql
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
pnwc=> \i test.sql
CREATE FUNCTION
CREATE FUNCTION
pnwc=> \df
(trimmed)
public | calc_share | record | cost real, n_shares integer,
OUTerror_term real, OUT result real
public | share_costs | void |
(1857 rows)
pnwc=> select share_costs();
Here I would expect to see 7 rows of output showing the 7
consecutive share values that were calculated. Instead:
ERROR: function calc_share(real, integer, real, real) does not exist
LINE 1: SELECT calc_share( $1 , $2 , $3 , $4 )
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT calc_share( $1 , $2 , $3 , $4 )
CONTEXT: PL/pgSQL function "share_costs" line 15 at PERFORM
My novice eyes don't see that the function doesn't exist, or that
the types of the parameters are not matched correctly to the
function declaration. What am I missing that is preventing this
function from working as I would like it to?
Thank you. I appreciate your time.
Regards,
Jim
Refinement:
I see from a closer reading of the docs that OUT parameters cannot
be passed to functions -- the error message was trying to tell me
that I had a mismatch in the NUMBER of parameters, not necessarily
their types. However, "ROUND()" forced me to make some type
changes and casts as necessary.
So the "calc_share" function is now declared as:
CREATE OR REPLACE FUNCTION calc_share( cost NUMERIC, N_shares INTEGER,
INOUT error_term REAL, OUT result NUMERIC ) AS $$
-- When called N consecutive times with identical values of
-- COST and N_SHARES, this routine will calculate N shares of a
-- value COST and keep track of the error term, so that some shares
-- may be one penny higher than other shares, but the sum of all the
-- shares will always match the total COST.
-- The caller must set error_term to 0 before the first call.
DECLARE
one_share REAL;
result NUMERIC;
BEGIN
one_share := cost / N_shares;
result := ROUND( CAST( one_share + error_term AS NUMERIC), 2 );
error_term := error_term + one_share - result;
END;
and I can get the routine to at least execute by using:
...
FOR i IN 1 .. shares LOOP
SELECT * FROM calc_share( cost, shares, error_term ) into error_term, one_share;
RAISE NOTICE 'i = %, share = %', i, one_share;
END LOOP;
...
But that SELECT syntax doesn't return any value into "one_share".
The output I get now is:
pnwc=> \i test.sql
CREATE FUNCTION
CREATE FUNCTION
pnwc=> select share_costs();
NOTICE: i = 1, share = <NULL>
NOTICE: i = 2, share = <NULL>
NOTICE: i = 3, share = <NULL>
NOTICE: i = 4, share = <NULL>
NOTICE: i = 5, share = <NULL>
NOTICE: i = 6, share = <NULL>
NOTICE: i = 7, share = <NULL>
share_costs
-------------
(1 row)
On Sun, Feb 07, 2010 at 05:33:29PM -0800, James Long wrote:
> At least, I hope this is a Novice question. It sure makes me
> feel like one! :) Please cc: me directly on replies, as I might
> miss a reply that goes only to the list.
>
> I'm running PostGreSQL 8.3.9 on FreeBSD 6.4-STABLE, but I doubt
> this is platform-dependent.
>
> I'm trying to solve what I'm sure is a common problem in the
> accounting world. A total cost C has to be split across N
> different accounts, and C is not necessarily divisible by N.
> Shares with fractions of pennies are not allowable, and to make
> the accounting balance, the sum of all the shares has to sum to
> exactly match the total cost C.
>
> It's like splitting a $90 lunch check between 7 people.
>
> This PHP code shows the math I'm using, and works great. For
> clarity to those not familiar with PHP, the &$error in the
> calc_share function declaration means that parameter $error is
> passed by reference, not by value. This is necessary because the
> calc_share function has to keep track of the cumulative error
> from one share to the next that arises from taking a real number
> and rounding it to two decimal places. Eventually, the
> cumulative error will be sufficient to bump a share up or down in
> value by one penny. I'm using OUT parameters in the PL/pgSQL
> version to achieve this result.
>
>
> <?php
> function calc_share( $amount, $shares, &$error )
> {
> $share = $amount / $shares;
> $result = round( $share + $error, 2 );
> $error += $share - $result;
> return $result;
> }
>
>
> $amount = 90;
> $shares = 7;
>
> $error_term = 0;
> $test_sum = 0;
>
> for ( $i = 0; $i < $shares; ) {
> ++$i;
> $one_share = calc_share( $amount, $shares, $error_term );
> print "i = " . $i . " " . $one_share . "\n";
> $test_sum = $test_sum + $one_share;
> }
> print "sum = " . $test_sum . "\n";
> ?>
>
> Here is my attempt to implement this in PL/PGSQL.
>
> The commented-out UPDATE query is how I would like to use this
> function in a real-world application (given that there are exactly 7
> rows in my table WHERE reference = 'SHARE').
>
> But for now, I'm using the FOR loop and the RAISE NOTICE just to
> try to see what's going on, and whether my algorithm is working
> correctly.
>
> It's not.
>
>
> --- begin test.sql
> CREATE OR REPLACE FUNCTION calc_share( cost REAL, N_shares INTEGER,
> OUT error_term REAL, OUT result REAL ) AS $$
>
> -- When called N consecutive times with identical values of
> -- COST and N_SHARES, this routine will calculate N shares of a
> -- value COST and keep track of the error term, so that some shares
> -- may be one penny higher than other shares, but the sum of all the
> -- shares will always match the total COST.
>
> -- The caller must set error_term to 0 before the first call.
>
> DECLARE
> one_share REAL;
> result REAL;
>
> BEGIN
> one_share := cost / N_shares;
> result := ROUND( one_share + error_term, 2 );
> error_term := error_term + one_share - result;
> END;
>
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION share_costs() RETURNS VOID AS $$
>
> DECLARE
> error_term REAL;
> shares INTEGER;
> i INTEGER;
> cost REAL;
> one_share REAL;
>
> BEGIN
> error_term := 0;
> cost := 90;
> shares := 7;
>
> -- update my_table set amount = calc_share( cost, shares, error_term ) where reference = 'SHARE';
>
> error_term := 0;
> FOR i IN 1 .. shares LOOP
> PERFORM calc_share( cost, shares, error_term, one_share );
> RAISE NOTICE 'i = %, share = %', i, one_share;
> END LOOP;
> END;
>
> $$ LANGUAGE PLPGSQL;
> --- end test.sql
>
>
> I'm trying to invoke this code thusly:
>
> $ psql
> Welcome to psql 8.3.9, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help with psql commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> pnwc=> \i test.sql
> CREATE FUNCTION
> CREATE FUNCTION
> pnwc=> \df
> (trimmed)
> public | calc_share | record | cost real, n_shares
integer,OUT error_term real, OUT result real
> public | share_costs | void |
> (1857 rows)
>
> pnwc=> select share_costs();
>
>
> Here I would expect to see 7 rows of output showing the 7
> consecutive share values that were calculated. Instead:
>
> ERROR: function calc_share(real, integer, real, real) does not exist
> LINE 1: SELECT calc_share( $1 , $2 , $3 , $4 )
> ^
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY: SELECT calc_share( $1 , $2 , $3 , $4 )
> CONTEXT: PL/pgSQL function "share_costs" line 15 at PERFORM
>
>
> My novice eyes don't see that the function doesn't exist, or that
> the types of the parameters are not matched correctly to the
> function declaration. What am I missing that is preventing this
> function from working as I would like it to?
>
> Thank you. I appreciate your time.
>
>
> Regards,
>
> Jim
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
James Long <pgsql-novice@museum.rain.com> writes:
> So the "calc_share" function is now declared as:
> CREATE OR REPLACE FUNCTION calc_share( cost NUMERIC, N_shares INTEGER,
> INOUT error_term REAL, OUT result NUMERIC ) AS $$
> -- When called N consecutive times with identical values of
> -- COST and N_SHARES, this routine will calculate N shares of a
> -- value COST and keep track of the error term, so that some shares
> -- may be one penny higher than other shares, but the sum of all the
> -- shares will always match the total COST.
> -- The caller must set error_term to 0 before the first call.
> DECLARE
> one_share REAL;
> result NUMERIC;
> BEGIN
> one_share := cost / N_shares;
> result := ROUND( CAST( one_share + error_term AS NUMERIC), 2 );
> error_term := error_term + one_share - result;
> END;
Hi James,
I think your problem is that you've got a local variable "result"
masking the OUT parameter. The assignment in the function body
assigns to that local variable, not to the OUT parameter.
BTW, you might also have some issues around having multiple versions
of calc_share() with different parameter lists --- you mentioned
having "cost" declared as both numeric and real. You might be seeing
the thing call a different version than you were expecting.
regards, tom lane
On Mon, Feb 08, 2010 at 01:51:16AM -0500, Tom Lane wrote: > James Long <pgsql-novice@museum.rain.com> writes: > > So the "calc_share" function is now declared as: Actually, error_term is now NUMERIC also: > > CREATE OR REPLACE FUNCTION calc_share( cost NUMERIC, N_shares INTEGER, > > INOUT error_term REAL, OUT result NUMERIC ) AS $$ > > > -- When called N consecutive times with identical values of > > -- COST and N_SHARES, this routine will calculate N shares of a > > -- value COST and keep track of the error term, so that some shares > > -- may be one penny higher than other shares, but the sum of all the > > -- shares will always match the total COST. > > > -- The caller must set error_term to 0 before the first call. > > > DECLARE > > one_share REAL; > > result NUMERIC; > > > BEGIN > > one_share := cost / N_shares; > > result := ROUND( CAST( one_share + error_term AS NUMERIC), 2 ); > > error_term := error_term + one_share - result; > > END; > > Hi James, > > I think your problem is that you've got a local variable "result" > masking the OUT parameter. The assignment in the function body > assigns to that local variable, not to the OUT parameter. > > BTW, you might also have some issues around having multiple versions > of calc_share() with different parameter lists --- you mentioned > having "cost" declared as both numeric and real. You might be seeing > the thing call a different version than you were expecting. > > regards, tom lane Gee, that duplication of the 'result' parameter should have been obvious. Thank you for pointing it out. And yes, I did a \df and saw that I did have a number of overloaded calc_share() functions with various parameter types. After removing the local declaration of 'result' and cleaning out the extraneous calc_share() definitions, it now works in debug mode: pnwc=> \i test.sql CREATE FUNCTION CREATE FUNCTION pnwc=> select share_costs(); NOTICE: i = 1, share = 12.86 NOTICE: i = 2, share = 12.85 NOTICE: i = 3, share = 12.86 NOTICE: i = 4, share = 12.86 NOTICE: i = 5, share = 12.86 NOTICE: i = 6, share = 12.85 NOTICE: i = 7, share = 12.86 share_costs ------------- (1 row) Thank you very much! Jim
Pardon the edit, but I'll delete the previous quotations, since
the issues of syntax and functionality are resolved. Thank you, Tom.
The next problem is simplicity, or elegance, if you like.
I would like the code to read like this:
CREATE OR REPLACE FUNCTION share_cost( cost NUMERIC ) RETURNS VOID AS $$
-- Spread the amount COST across a number of records WHERE reference = 'SHARE'
-- increment the AMOUNT field by the amount of a nearly-equal share, so that
-- the sum of the shares exactly equals COST.
DECLARE
shares INTEGER;
error_term NUMERIC;
BEGIN
SELECT SUM(1) from temp WHERE reference = 'SHARE' into shares;
error_term := 0;
UPDATE temp SET amount = amount + calc_share( cost, shares, error_term )
WHERE reference = 'SHARE';
END;
$$ LANGUAGE PLPGSQL;
This example has the advantage of not requiring a primary key on
my temporary table, since the UPDATE statement ensures that each
record is processed in turn, with no ambiguity as to which record
is being updated.
However, the "calc_share" function has one INOUT parameter
"error_term" and an OUT parameter "result". From what I gather
so far, PL/pgSQL does not allow a function with OUT or INOUT
parameters to return a scalar result value. Based on that
understanding, my code looks like:
CREATE OR REPLACE FUNCTION share_cost( cost NUMERIC ) RETURNS VOID AS $$
-- Spread the amount COST across a number of records WHERE reference = 'SHARE'
-- increment the AMOUNT field by the amount of a nearly-equal share, so that
-- the sum of the shares exactly equals COST.
DECLARE
shares INTEGER;
error_term NUMERIC;
one_share NUMERIC;
share_record RECORD;
BEGIN
SELECT SUM(1) from temp WHERE reference = 'SHARE' into shares;
error_term := 0;
FOR share_record IN SELECT * FROM temp WHERE reference = 'SHARE' LOOP
SELECT error_term, result FROM calc_share( cost, shares, error_term ) INTO error_term, one_share;
UPDATE temp SET amount = amount + one_share
WHERE temp.acct_id = share_record.acct_id;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
So the simple UPDATE statement in the first example becomes a
somewhat clunky FOR loop in the second example, and the second
example also requires a primary key on acct_id to ensure that the
UPDATE and the FOR loop reference the same record.
Is that as good as this can get, or is there a simpler way, more
along the lines of the first version?
Thanks again for the education.
Jim
James Long <pgsql-novice@museum.rain.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
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@museum.rain.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
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
On 2010-02-08, James Long <pgsql-novice@museum.rain.com> wrote:
Newsgroups: gmane.comp.db.postgresql.novice
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: Novice PL/pgSQL question and example
References: <20100208013329.GA95096@ns.umpquanet.com>
Organization: Dis (not Dat) Organisation
Followup-To:
X-Face:
?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8"9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
On 2010-02-08, James Long <pgsql-novice@museum.rain.com> wrote:
> At least, I hope this is a Novice question. It sure makes me
> feel like one! :) Please cc: me directly on replies, as I might
> miss a reply that goes only to the list.
>
> I'm running PostGreSQL 8.3.9 on FreeBSD 6.4-STABLE, but I doubt
> this is platform-dependent.
>
> I'm trying to solve what I'm sure is a common problem in the
> accounting world. A total cost C has to be split across N
> different accounts, and C is not necessarily divisible by N.
> Shares with fractions of pennies are not allowable, and to make
> the accounting balance, the sum of all the shares has to sum to
> exactly match the total cost C.
>
> It's like splitting a $90 lunch check between 7 people.
>
> This PHP code shows the math I'm using, and works great.
here's different way to do it.
share ( pieces , people, index ) = floor (pieces * (index + 1) / people ) - floor ( pieces * index / people)
you don't need to remember an error just increase the index each time.