Record variable not behaving as expected (bug?)
От | Postgres User |
---|---|
Тема | Record variable not behaving as expected (bug?) |
Дата | |
Msg-id | b88c3460711300023g714e5d15tf8b548567e8f44a4@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Record variable not behaving as expected (bug?)
|
Список | 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';
В списке pgsql-general по дате отправления: