Skip site navigation (1) Skip section navigation (2)

Re: Novice PL/pgSQL question and example

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James Long <pgsql-novice(at)museum(dot)rain(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Novice PL/pgSQL question and example
Date: 2010-02-08 06:51:16
Message-ID: 25053.1265611876@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
James Long <pgsql-novice(at)museum(dot)rain(dot)com> writes:
> 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;

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

In response to

Responses

pgsql-novice by date

Next:From: Александър ШоповDate: 2010-02-08 06:53:59
Subject: How to use a db in UTF-8 encoding under Windows
Previous:From: Tom LaneDate: 2010-02-08 06:19:49
Subject: Re: Incomplete pg_dump operation

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