Postgres Pro
Downloads
Home   >   mailing lists

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

Record variable not behaving as expected (bug?)

From
"Postgres User"
Date:
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';

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

From
Martijn van Oosterhout
Date:
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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Attachment

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

From
"Postgres User"
Date:
8.2  /  Windows   (a development-only pc)

On Nov 30, 2007 12:50 AM, Martijn van Oosterhout <kleptog@svana.org> wrote:
> 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/
> > Those who make peaceful revolution impossible will make violent revolution inevitable.
> >  -- John F Kennedy
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHT87KIB7bNG8LQkwRAuzqAJ9jdaDv/rxz5pG8bdYvO9suxZLGZACeL6BY
> ZMvLJ5nKREBIsBrdk4nE748=
> =/aEm
> -----END PGP SIGNATURE-----
>
>

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

From
Tom Lane
Date:
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

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

From
"Postgres User"
Date:
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 Nov 30, 2007 8:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>

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

From
Martijn van Oosterhout
Date:
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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Attachment

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

From
"Postgres User"
Date:
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.


On Nov 30, 2007 8:25 AM, Martijn van Oosterhout <kleptog@svana.org> wrote:
> 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/
> > Those who make peaceful revolution impossible will make violent revolution inevitable.
> >  -- John F Kennedy
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHUDmUIB7bNG8LQkwRAvNMAKCQl8+bPo3bca/a33T+WVfQ/Ng2yQCdG+H5
> wZyc/alsznWsWck20lheR00=
> =RVrJ
> -----END PGP SIGNATURE-----
>
>

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

From
"Postgres User"
Date:
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 Nov 30, 2007 12:31 PM, Postgres User <postgres.developer@gmail.com> wrote:
> 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 the output:
>
> select divide()
>        ?column?
> ------------------------
>  0
>
> > 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.

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

From
Martijn van Oosterhout
Date:
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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Attachment