Record variable not behaving as expected (bug?)

On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:

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.

What version are you running. On my 8.1.9 test system it returns -1.00
as expected.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

8.2 / Windows (a development-only pc)

Martijn van Oosterhout <kleptog@svana.org> writes:

On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:

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.

What version are you running. On my 8.1.9 test system it returns -1.00
as expected.

Works for me too, in all branches back to 8.0. However, I noticed that
the test function references "test.table2" not just "table2", which
makes me wonder if maybe this is picking up some other table2 than the
OP thinks. A test.table2 with integer columns would explain the result.

regards, tom lane

tom- did you test this on wndows? you can ignore the namespace- i'm
using it consistently but removed from the test code to simplify

this problem occurs repeatedly in my code. my guess is that it's a
bug in the windows build.

On Fri, Nov 30, 2007 at 08:20:30AM -0800, Postgres User wrote:

tom- did you test this on wndows? you can ignore the namespace- i'm
using it consistently but removed from the test code to simplify

this problem occurs repeatedly in my code. my guess is that it's a
bug in the windows build.

Seems rather unlikely to affect just windows. Can you post a script
that you can run against a blank database that shows the problem.
complete with output on your machine.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Sure, I'd be glad to provide any additional code or info that I can.
This issue took me quite awhile to track down from the 200-line
function that was breaking.

When I execute the function defined above, here's what I see returned:

select divide()
?column?
------------------------
0

Hopefully someone can test on Windows to validate.

Sorry, here's the code:

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

On Fri, Nov 30, 2007 at 12:34:57PM -0800, Postgres User wrote:

Sorry, here's the code:

<snip>

The code you posted won't work on a blank database, I needed to delete
the test prefix to table2. I've adjusted it to be hopefully what you
did. My output is:
---
CREATE TABLE
CREATE FUNCTION
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

divide
--------
-1.00
(1 row)

DROP FUNCTION
DROP TABLE
---

Can you try the attached script on a *nlank* database? It'll give us
more info about your system.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/