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: pgsql-novice(at)postgresql(dot)org
Subject: Re: Novice PL/pgSQL question and example
Date: 2010-02-08 03:14:46
Message-ID: 20100208031446.GA99171@ns.umpquanet.com (view raw or flat)
Thread:
Lists: 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(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: Mary AndersonDate: 2010-02-08 04:51:31
Subject: Newbie question about blobs and bytea
Previous:From: Randy BDate: 2010-02-08 02:13:29
Subject: My new song video

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