Skip site navigation (1)
Skip section navigation (2)
## Re: Novice PL/pgSQL question and example

### In response to

### Responses

### pgsql-novice by date

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

- Novice PL/pgSQL question and example at 2010-02-08 01:33:29 from James Long

- Re: Novice PL/pgSQL question and example at 2010-02-08 06:51:16 from Tom Lane

Next: From:Mary AndersonDate:2010-02-08 04:51:31Subject: Newbie question about blobs and byteaPrevious: From: Randy BDate: 2010-02-08 02:13:29Subject: My new song video