Postgres Pro
Downloads
Home   >   mailing lists

Thread: Division

Division

From
Robert Fitzpatrick
Date:
I am having a problem gettig a percent via division. Below is the first
part of my trigger function where pct returns 0.00, instead of the
expected 0.50. If I try without dimensions to the numeric variable, I
just get 0. What is the correct way to accomplish the percent?

CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
AS'
DECLARE
  repar text[];
  pct numeric(3,2);
  noreps integer;

BEGIN
  repar := string_to_array($1,''-'');
  noreps := array_upper(repar,1);
  pct := 1/noreps;
  RETURN pct;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

bachman=# select issue('AA-BB');
 issue
-------
  0.00
(1 row)

Re: Division

From
Peter Eisentraut
Date:
Robert Fitzpatrick wrote:
> I am having a problem gettig a percent via division. Below is the
> first part of my trigger function where pct returns 0.00, instead of
> the expected 0.50. If I try without dimensions to the numeric
> variable, I just get 0. What is the correct way to accomplish the
> percent?

You are dividing an integer by an integer, and if the first integer is 1
then the result is often 0.  You need to use numeric for your noreps
variable.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Division

From
Robert Ftizpatrick
Date:
On Fri, 2005-11-04 at 18:39 +0100, Tino Wildenhain wrote:
> > CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
> > AS'
> > DECLARE
> >   repar text[];
> >   pct numeric(3,2);
> >   noreps integer;
> >
> > BEGIN
> >   repar := string_to_array($1,''-'');
> >   noreps := array_upper(repar,1);
> >   pct := 1/noreps;
>
> you have noreps integer, 1 is integer too so division almost
> every time will result in 0. (Unless noreps is 0)
> Then 0 will be casted to numeric(3,2) which is 0.00.
>
> >   RETURN pct;
> > END;
> > 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >
> > bachman=# select issue('AA-BB');
> >  issue
> > -------
> >   0.00
> > (1 row)
>
> Btw, apart from the integer problem you are facing, what is
> the whole point of that function (when it finally 'works')?
>

Thanks, of course, can't see for the confusion :(

It is part of a trigger that breaks apart an incoming CSV field by
dashes into an array and inserts the percent of the transaction that
belongs to each element of the array. So, if there are 2 elements, each
get 50%, if 4, then they will get 25%, so on.

Thanks again...

--
Robert

Re: Division

From
Tino Wildenhain
Date:
Am Freitag, den 04.11.2005, 12:13 -0500 schrieb Robert Fitzpatrick:
> I am having a problem gettig a percent via division. Below is the first
> part of my trigger function where pct returns 0.00, instead of the
> expected 0.50. If I try without dimensions to the numeric variable, I
> just get 0. What is the correct way to accomplish the percent?
>
> CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
> AS'
> DECLARE
>   repar text[];
>   pct numeric(3,2);
>   noreps integer;
>
> BEGIN
>   repar := string_to_array($1,''-'');
>   noreps := array_upper(repar,1);
>   pct := 1/noreps;

you have noreps integer, 1 is integer too so division almost
every time will result in 0. (Unless noreps is 0)
Then 0 will be casted to numeric(3,2) which is 0.00.

>   RETURN pct;
> END;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> bachman=# select issue('AA-BB');
>  issue
> -------
>   0.00
> (1 row)

Btw, apart from the integer problem you are facing, what is
the whole point of that function (when it finally 'works')?

Regards
Tino