As reported in pgsql-bugs, in 9, a set returning function will raise an
error "Returned type .. does not match expected type .." when the
source type does not exactly match the target type. For example
VARCHAR(3) to VARCHAR(4) or NUMERIC(4,2) to NUMERIC(5,2). Previously,
this was not an issue.
It was pointed out in pgsql-bugs that this new behavior was expected and
the result of the logic used by ConvertRowtypeExpr. The old behavior is
considered wrong.
To me, it seems like in most other parts of Pg types are
cast sensibly without complaint. For example, 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(5,3))) AS bar(m) ON foo.n = bar.m;
The values are rounded and cast; Same with varchar of various sizes.
Also note that returning a table with a different type still works in 9..
CREATE TABLE a_table ( val VARCHAR(3) );
INSERT INTO a_table VALUES ('abc');
CREATE FUNCTION check_varchar() RETURNS
TABLE (val VARCHAR(4)) AS
$$
DECLARE
BEGIN SELECT * INTO val FROM a_table; RETURN NEXT; RETURN;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
SELECT * FROM check_varchar();
-- above works in pg 9
-- while the more traditional function returning SETOF does not..
CREATE TABLE b_table ( val VARCHAR(4) );
DROP FUNCTION check_varchar();
CREATE FUNCTION check_varchar() 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_varchar();
Regards,
-Noel Proffitt
