Division

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

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/

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