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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
