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