Skip site navigation (1) Skip section navigation (2)

Type Mismatch Error in Set Returning Functions

From: Noel Proffitt <noelp(at)calpacs(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Type Mismatch Error in Set Returning Functions
Date: 2010-03-11 06:53:58
Message-ID: 1268290438.6076.255.camel@keg (view raw or flat)
Thread:
Lists: pgsql-hackers
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


pgsql-hackers by date

Next:From: Simon RiggsDate: 2010-03-11 08:30:36
Subject: Re: PD_ALL_VISIBLE flag error on 9.0 alpha 4
Previous:From: Josh BerkusDate: 2010-03-11 05:58:35
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group