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: 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 (view raw or flat)
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


In response to

pgsql-novice by date

Next:From: Ognjen BlagojevicDate: 2010-02-08 09:02:47
Subject: Re: Newbie question about blobs and bytea
Previous:From: peterDate: 2010-02-08 07:15:33
Subject: Re: Incomplete pg_dump operation

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