Re: Novice PL/pgSQL question and example

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice