Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Hi
I've started testing our applications against PostgreSQL 9.0 beta2 and found
that this function now does not work anymore(rewritten to be as small and
anonymous as possible)
CREATE TYPE test_type AS(
product text,
amount numeric(30,4)
);
CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$
BEGIN
RETURN QUERY SELECT 'test'::text, 30.2::numeric;
END;
$$ LANGUAGE plpgsql STABLE;
Output in postgresql 8.4:
postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit
(1 row)
postgres=# SELECT test_func();
test_func
-------------
(test,30.2)
(1 row)
postgres=#
And in postgresql 9.0 beta2
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0beta2 on x86_64-apple-darwin10.4.0, compiled by GCC
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5659), 64-bit
(1 row)
postgres=# \set VERBOSITY verbose
postgres=# SELECT test_func();
ERROR: 42804: structure of query does not match function result type
DETAIL: Returned type numeric does not match expected type numeric(30,4) in
column 2.
CONTEXT: PL/pgSQL function "test_func" line 2 at RETURN QUERY
LOCATION: convert_tuples_by_position, tupconvert.c:112
postgres=#
Currently unning Mac OS X 10.6
Marcel Asio
Network & System Administrator
Redbet Technology
Mobile: +46 (0)709 13 04 01
Work: +46 (0)8 12 09 99 41
marcel.asio@redbet.com
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
Marcel Asio <marcel.asio@redbet.com> writes:
I've started testing our applications against PostgreSQL 9.0 beta2 and found
that this function now does not work anymore(rewritten to be as small and
anonymous as possible)
CREATE TYPE test_type AS(
product text,
amount numeric(30,4)
);
CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$
BEGIN
RETURN QUERY SELECT 'test'::text, 30.2::numeric;
END;
$$ LANGUAGE plpgsql STABLE;
You need to actually coerce the 30.2 to numeric(30,4), not just numeric.
The former behavior wasn't self-consistent.
regards, tom lane
Hi Tom,
Yes I managed to figured that out, but when was this changed?
It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes.
On Jun 29, 2010, at 17:32 , Tom Lane wrote:
Marcel Asio <marcel.asio@redbet.com> writes:
I've started testing our applications against PostgreSQL 9.0 beta2 and found
that this function now does not work anymore(rewritten to be as small and
anonymous as possible)
CREATE TYPE test_type AS(
product text,
amount numeric(30,4)
);
CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$
BEGIN
RETURN QUERY SELECT 'test'::text, 30.2::numeric;
END;
$$ LANGUAGE plpgsql STABLE;You need to actually coerce the 30.2 to numeric(30,4), not just numeric.
The former behavior wasn't self-consistent.regards, tom lane
Regards
Marcel Asio
Network & System Administrator
Redbet Technology
Mobile: +46 (0)709 13 04 01
Work: +46 (0)8 12 09 99 41
marcel.asio@redbet.com
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Marcel Asio <marcel.asio@redbet.com> writes:
Yes I managed to figured that out, but when was this changed?
It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes.
The 9.0 release notes are not really up to snuff yet :-(. The only
thing in the notes about it is "Allow PL/pgSQL to handle row types with
dropped columns" which is a rather inadequate description of the
consequences of that patch. We're probably going to need to call it out
as an incompatibility.
regards, tom lane
I was suspecting that it was incompatibility that hadn't been documented
correctly, just wanted to make sure that this was the case.
It probably should go into the release notes too since it is bound to affect
others....
Thankfully
Marcel Asio
Network & System Administrator
Redbet Technology
Mobile: +46 (0)709 13 04 01
Work: +46 (0)8 12 09 99 41
marcel.asio@redbet.com
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
On 29 June 2010 19:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Marcel Asio <marcel.asio@redbet.com> writes:
Yes I managed to figured that out, but when was this changed?
It was working in 8.4 but not 9.0 and I could not find anything about
this in the release notes.
The 9.0 release notes are not really up to snuff yet :-(. The only
thing in the notes about it is "Allow PL/pgSQL to handle row types with
dropped columns" which is a rather inadequate description of the
consequences of that patch. We're probably going to need to call it out
as an incompatibility.regards, tom lane
Marcel Asio <marcel.asio@redbet.com> writes:
I was suspecting that it was incompatibility that hadn't been documented
correctly, just wanted to make sure that this was the case.
It probably should go into the release notes too since it is bound to affect
others....
Yeah, done.
regards, tom lane
Awesome
Thanks for the help
Marcel Asio
Network & System Administrator
Redbet Technology
Mobile: +46 (0)709 13 04 01
Work: +46 (0)8 12 09 99 41
marcel.asio@redbet.com
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
On 29 June 2010 23:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Marcel Asio <marcel.asio@redbet.com> writes:
I was suspecting that it was incompatibility that hadn't been documented
correctly, just wanted to make sure that this was the case.It probably should go into the release notes too since it is bound to
affect
others....
Yeah, done.
regards, tom lane