Record variable not behaving as expected (bug?)

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';

Responses

Browse pgsql-general by date

  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