8.3 migration helper
8.3 dropped some implicit casts. I didn't watch discus about it, but I am thinking so there are two reasons. First - it shows some programmer's mistakes, second - it warns against wrong written predicates. I can speak, so all it does well. When we migrated older application, we found lot of nonsense: typically numeric data are stored in varchar column or deprecated style (every constant is string literal) like:
any_numeric_column = 'any_number' ... -- deprecated
any_numeric_column = any_number ... -- good style
Peter Eisentraut wrote module for 8.3, that enable all disabled implicit casts. This helps with migration, but doesn't help with question, where disabled casts are used in application. I wrote function, that adds warning to theses casts:
CREATE OR REPLACE FUNCTION generator_81_casts()
RETURNS void AS $$
DECLARE
src varchar[] := '{integer,smallint,oid,date,double precision,real,time with time zone, time without time zone, timestamp with time zone, interval,bigint,numeric,timestamp without time zone}';
fn varchar[] := '{int4out,int2out,oidout,date_out,float8out,float4out,timetz_out,time_out,timestamptz_out,interval_out,int8out,numeric_out,timestamp_out}';
fn_name varchar;
fn_msg varchar; fn_body varchar;
BEGIN
FOR i IN array_lower(src,1)..array_upper(src,1) LOOP
fn_name := 'aux_cast_func_' || replace(src[i],' ','_') ||'_to_text';
fn_msg := '''using obsolete implicit casting from ' || src[i] || ' to text''';
fn_body := 'CREATE OR REPLACE FUNCTION '|| fn_name || '(' || src[i] ||') RETURNS text AS $_$ BEGIN RAISE WARNING '
|| fn_msg || ';RETURN textin(' || fn[i] || '($1)); END; $_$ LANGUAGE plpgsql IMMUTABLE';
EXECUTE fn_body;
-- for 8.1
--EXECUTE 'UPDATE pg_cast SET castfunc = ''' || fn_name || '''::regproc WHERE castsource = ''' || src[i] || '''::regtype AND casttarget = ''text''::regtype';
DROP CAST 'CREATE CAST (' || src[i] || ' AS text)';
EXECUTE 'CREATE CAST (' || src[i] || ' AS text) WITH FUNCTION ' || fn_name || '(' || src[i] || ') AS IMPLICIT';
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT generator_81_casts();