Re: Novice PL/pgSQL question and example

On Mon, Feb 08, 2010 at 01:51:16AM -0500, Tom Lane wrote:
> James Long <pgsql-novice(at)museum(dot)rain(dot)com> writes:
> > So the "calc_share" function is now declared as:

Actually, error_term is now NUMERIC also:

> > 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;
>
> Hi James,
>
> I think your problem is that you've got a local variable "result"
> masking the OUT parameter. The assignment in the function body
> assigns to that local variable, not to the OUT parameter.
>
> BTW, you might also have some issues around having multiple versions
> of calc_share() with different parameter lists --- you mentioned
> having "cost" declared as both numeric and real. You might be seeing
> the thing call a different version than you were expecting.
>
> regards, tom lane

Gee, that duplication of the 'result' parameter should have been obvious.
Thank you for pointing it out. And yes, I did a \df and saw that I did
have a number of overloaded calc_share() functions with various parameter
types. After removing the local declaration of 'result' and cleaning out
the extraneous calc_share() definitions, it now works in debug mode:

pnwc=> \i test.sql
CREATE FUNCTION
CREATE FUNCTION
pnwc=> select share_costs();
NOTICE: i = 1, share = 12.86
NOTICE: i = 2, share = 12.85
NOTICE: i = 3, share = 12.86
NOTICE: i = 4, share = 12.86
NOTICE: i = 5, share = 12.86
NOTICE: i = 6, share = 12.85
NOTICE: i = 7, share = 12.86
share_costs
-------------

(1 row)

Thank you very much!

Jim