Sorry, here's the code:
CREATE TABLE table2 (
"s_val" NUMERIC(6,2),
"e_val" NUMERIC(6,2)
);
CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
retval numeric(6,2);
rec record;
begin
SELECT * INTO rec FROM test.table2 LIMIT 0;
rec.s_val = 100.0;
rec.e_val = 101.0;
-- returns correct value w/ casting:
--retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2)) * 100;
-- returns incorrect value, as if fields have invalid datatypes:
retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;
return retval;
end
$body$
LANGUAGE 'plpgsql';
On Nov 30, 2007 12:31 PM, Postgres User <postgres.developer@gmail.com> wrote:
> Sure, I'd be glad to provide any additional code or info that I can.
> This issue took me quite awhile to track down from the 200-line
> function that was breaking.
>
> When I execute the function defined above, here's the output:
>
> select divide()
> ?column?
> ------------------------
> 0
>
> > Seems rather unlikely to affect just windows. Can you post a script
> > that you can run against a blank database that shows the problem.
> > complete with output on your machine.