Novice PL/pgSQL question and example - Mailing list pgsql-novice

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