From: | "Postgres User" <postgres(dot)developer(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Record variable not behaving as expected (bug?) |
Date: | 2007-11-30 08:23:31 |
Message-ID: | b88c3460711300023g714e5d15tf8b548567e8f44a4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
According to the docs, record variables "take on the actual row
structure of the row they are assigned during a SELECT or FOR
command."
However, I have found that my record variable is not assigned proper
field-level datatypes. As a result, I'm unable to write basic math
calcs in pg/sql without a lot of typecasting.
When I execute the function below, a basic math statement fails unless
I explicitly typecast the record's field values. This isn't what I
expected; Postgresql should correctly typecast each field in the
record var automatically at the SELECT statement.
(Note: did not test with a row variable, and I prefer to use the
record datatype)
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-11-30 08:50:18 | Re: Record variable not behaving as expected (bug?) |
Previous Message | Postgres User | 2007-11-30 08:04:00 | Re: Simple math statement - problem |