Обсуждение: [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@calpacs.org> To: pgsql-testers@postgresql.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@agliodbs.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