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'; ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
