Contents
Auto-casting
In the 8.3 release (http://www.postgresql.org/docs/8.3/static/release-8-3.html#AEN86487) a change was made such that "Non-character data types are no longer automatically cast to TEXT".
For existing code this can cause problems because old functions of the form:
CREATE OR REPLACE FUNCTION i_see_string_people( text ) RETURNS text AS $$ BEGIN RETURN 'SPOTTED: ' || $1; END; $$ LANGUAGE plpgsql
Which could previously be called like this:
> SELECT i_see_string_people(42); i_see_string_people --------------------- SPOTTED: 42 (1 row)
Now results in an error:
> SELECT i_see_string_people(42);
ERROR: function i_see_string_people(integer) does not exist
LINE 1: SELECT i_see_string_people(42);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
The most correct solution is to go back and fix any of these auto-casts in the code. The reason that they were removed is that it was found that automatic casting was causing surprising and difficult to locate bugs. See the release notes for examples of the problem.
But the most correct solution is not necessarily the quickest one - should you wish to throw caution to the wind you can explicitally define CAST's. Better yet you can define them and log whenever they are called which might offer a handy way to find where the auto-casting was being used legitimately.
For example - casting integers to text could be done the following way:
CREATE OR REPLACE FUNCTION inttotext( int ) RETURNS text as $$
DECLARE
num_to_cast ALIAS for $1;
BEGIN
RAISE WARNING 'Auto-casting int to text: %', num_to_cast;
RETURN num_to_cast;
END
$$ LANGUAGE plpgsql;
DROP CAST IF EXISTS ( int AS text );
CREATE CAST ( int AS text ) WITH FUNCTION inttotext(int) AS IMPLICIT;
Note that you may need to log in as an admin user to CREATE CAST if you do not have sufficient privileges and you end up with an error like "ERROR: must be owner of type pg_catalog.int4 or type text".
Having created the CAST you should now see:
> SELECT i_see_string_people(42); WARNING: Auto-casting int to text: 42 i_see_string_people --------------------- SPOTTED: 42 (1 row)