Re: Record variable not behaving as expected (bug?)

[Martijn van Oosterhout <kleptog@xxxxxxxxx>]

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@xxxxxxxxx>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

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

select version();
select divide();
drop function divide();
drop table table2;

Attachment: signature.asc
Description: Digital signature