Re: [Fwd: [TESTERS] Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function]

All,

I tested Noel's test case and verified that it does, in fact, break.
And functions on 8.4.

--Josh Berkus

-------- Original Message --------
Subject: [TESTERS] Numerics of diffrent scales Raises Type Mismatch
Error in a Set Returning Function
Date: Tue, 2 Mar 2010 20:07:07 -0800
From: Noel Proffitt <noelp(at)calpacs(dot)org>
To: pgsql-testers(at)postgresql(dot)org

[TEST REPORT]

[Release]: 9.0 Alpha 4

[Test Type]: feature

[Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
RETURNING FUNCTION

[Platform]: Linux RHEL/Fedora

[Parameters]:

[Failure]: Yes

[Results]: ERROR: wrong record type supplied in RETURN NEXT
DETAIL: Returned type numeric does not match expected type
numeric(14,2) in column 1.
CONTEXT: PL/pgSQL function "check_numeric" line 5 at RETURN NEXT

-- Test case

CREATE TABLE a_table ( val NUMERIC );
INSERT INTO a_table VALUES (42);

CREATE TABLE b_table ( val NUMERIC(14,2) );

CREATE OR REPLACE FUNCTION check_numeric() RETURNS SETOF b_table AS
$$
DECLARE
myrec RECORD;
BEGIN
SELECT * INTO myrec FROM a_table;
RETURN NEXT myrec;
RETURN;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
SELECT * FROM check_numeric();

[Comments]: Works in Pg 8.3 and 8.4. Didn't see a change in the release
notes notifying of the behavior change.

-
HOWTO Alpha/Beta Test:
http://wiki.postgresql.org/wiki/HowToBetaTest
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-testers

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I tested Noel's test case and verified that it does, in fact, break.
> And functions on 8.4.

This is an intentional change, as the previous behavior is obviously
wrong. Try putting in a value that does not conform to numeric(14,2),
such as 42.7777. It doesn't get converted.

In some future version we might want to think about applying actual data
conversions in such contexts, but right now the tuple conversion code
just insists on exact datatype matches.

The reason for the behavioral change is that plpgsql, which formerly
had really crummy tuple conversion logic with a whole bunch of other
deficiencies besides this one, now shares the logic used by
ConvertRowtypeExpr.

regards, tom lane

On 3/6/10 5:45 PM, Tom Lane wrote:
> The reason for the behavioral change is that plpgsql, which formerly
> had really crummy tuple conversion logic with a whole bunch of other
> deficiencies besides this one, now shares the logic used by
> ConvertRowtypeExpr.

Oh, yes, of course. Should have thought of that.

--Josh Berkus