From: | James Long <pgsql-novice(at)museum(dot)rain(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Novice PL/pgSQL question and example |
Date: | 2010-02-08 07:57:45 |
Message-ID: | 20100208075745.GA14066@ns.umpquanet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ognjen Blagojevic | 2010-02-08 09:02:47 | Re: Newbie question about blobs and bytea |
Previous Message | peter | 2010-02-08 07:15:33 | Re: Incomplete pg_dump operation |