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

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