Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Marcel Asio <marcel(dot)asio(at)redbet(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: 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 15:32:45
Message-ID: 19198.1277825565@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Marcel Asio <marcel(dot)asio(at)redbet(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Marcel AsioDate: 2010-06-29 15:44:09
Subject: Re: Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Previous:From: Kevin GrittnerDate: 2010-06-29 14:03:57
Subject: Re: JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group