Lists: | pgsql-bugs |
---|
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | [Fwd: [TESTERS] Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function] |
Date: | 2010-03-07 01:12:43 |
Message-ID: | 4B92FD8B.1090005@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: [Fwd: [TESTERS] Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function] |
Date: | 2010-03-07 01:45:54 |
Message-ID: | 6645.1267926354@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: [Fwd: [TESTERS] Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function] |
Date: | 2010-03-07 18:01:31 |
Message-ID: | 4B93E9FB.7010909@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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