Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Record variable not behaving as expected (bug?)



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



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group