Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"

From: Marcel Asio <marcel(dot)asio(at)redbet(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Date: 2010-06-29 09:03:13
Message-ID: AANLkTikkB_dt-Uhz7iV3U8PZPeDpWPHF_jk9QLeQZfB2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(dot)asio(at)redbet(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2010-06-29 14:03:57 Re: JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Previous Message Saneesh Apte 2010-06-29 02:05:55 JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.