pg.ddx.io pgsql-testers@postgresql.org mailing list archive
help / color / mirror / Atom feedNumerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
5+ messages / 2 participants
[nested] [flat]
* Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-03 04:07 Noel Proffitt <noelp@calpacs.org>
2010-03-07 01:10 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Josh Berkus <josh@agliodbs.com>
2010-03-08 06:45 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
0 siblings, 2 replies; 5+ messages in thread
From: Noel Proffitt @ 2010-03-03 04:07 UTC (permalink / raw)
To: pgsql-testers
[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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
@ 2010-03-07 01:10 ` Josh Berkus <josh@agliodbs.com>
2010-03-07 23:08 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2010-03-07 01:10 UTC (permalink / raw)
To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers
On 3/2/10 8:07 PM, Noel Proffitt wrote:
> [TEST REPORT]
>
> [Release]: 9.0 Alpha 4
>
> [Test Type]: feature
>
> [Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
> RETURNING FUNCTION
Verified as a problem in 9.0.
--Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
2010-03-07 01:10 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Josh Berkus <josh@agliodbs.com>
@ 2010-03-07 23:08 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2010-03-07 23:08 UTC (permalink / raw)
To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers
On 3/6/10 5:10 PM, Josh Berkus wrote:
> On 3/2/10 8:07 PM, Noel Proffitt wrote:
>> [TEST REPORT]
>>
>> [Release]: 9.0 Alpha 4
>>
>> [Test Type]: feature
>>
>> [Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
>> RETURNING FUNCTION
>
> Verified as a problem in 9.0.
Per pgsql-bugs, it's an expected compatibility issue due to unifying the
SQL and PLPGSQL parsers. That is, the fact that plpgsql used to let you
do this was regarded as a bug, and was not consistent with the SQL
command line.
Worth noting in the release notes as part of a general class of
backwards-compatibility issues.
--Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
@ 2010-03-08 06:45 ` Noel Proffitt <noelp@calpacs.org>
2010-03-08 18:10 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Noel Proffitt @ 2010-03-08 06:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-testers
On Sun, 2010-03-07 at 15:08 -0800, Josh Berkus wrote:
> Per pgsql-bugs, it's an expected compatibility issue due to unifying the
> SQL and PLPGSQL parsers. That is, the fact that plpgsql used to let you
> do this was regarded as a bug, and was not consistent with the SQL
> command line.
>
> Worth noting in the release notes as part of a general class of
> backwards-compatibility issues.
>
> --Josh Berkus
>
Note that this behavior change affects seems to affect other types who's
size is different such as character varying.
I'm probably not understanding, but I'm not sure I follow what the
consistency issue is. It seems like in most other parts of PG, types are
cast sensibly without complaint.
In 9.0 and 8.4 we can do things like:
CREATE TABLE foo (n NUMERIC(10,2));
INSERT INTO foo values (42.777777::NUMERIC(12,2));
INSERT INTO foo values (42.777777::NUMERIC(8,2));
INSERT INTO foo values (42.777777::NUMERIC(14,8));
SELECT * FROM foo
JOIN (VALUES (42.78::NUMERIC)) AS bar(m) ON foo.n = bar.m;
The values are rounded and cast; Same with varchar of various sizes.
But when returning a setof things are now strictly checked. However,
when selecting into a record PL doesn't complain if the sizes don't
match the substructure. Also curious is that single value return types
are not too particular. For example:
CREATE OR REPLACE FUNCTION check_me() RETURNS VARCHAR(2) AS
$$
BEGIN
RETURN 42.7777::NUMERIC(7,4);
END;
$$ LANGUAGE 'plpgsql';
SELECT *,pg_typeof(check_me) FROM check_me();
check_me | pg_typeof
----------+-------------------
42.7777 | character varying
(1 row)
-Noel Proffitt
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
2010-03-08 06:45 ` Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
@ 2010-03-08 18:10 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2010-03-08 18:10 UTC (permalink / raw)
To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers
On 3/7/10 10:45 PM, Noel Proffitt wrote:
> On Sun, 2010-03-07 at 15:08 -0800, Josh Berkus wrote:
>> Per pgsql-bugs, it's an expected compatibility issue due to unifying the
>> SQL and PLPGSQL parsers. That is, the fact that plpgsql used to let you
>> do this was regarded as a bug, and was not consistent with the SQL
>> command line.
>>
>> Worth noting in the release notes as part of a general class of
>> backwards-compatibility issues.
>>
>> --Josh Berkus
>>
>
> Note that this behavior change affects seems to affect other types who's
> size is different such as character varying.
>
> I'm probably not understanding, but I'm not sure I follow what the
> consistency issue is. It seems like in most other parts of PG, types are
> cast sensibly without complaint.
Please take this up on pgsql-hackers.
--Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-03-08 18:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
2010-03-07 01:10 ` Josh Berkus <josh@agliodbs.com>
2010-03-07 23:08 ` Josh Berkus <josh@agliodbs.com>
2010-03-08 06:45 ` Noel Proffitt <noelp@calpacs.org>
2010-03-08 18:10 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox