From 76574ae0d301dd1441181e47540c3e274caf4609 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Tue, 8 Sep 2009 09:00:48 +0000 Subject: [PATCH 1/1] utility methods for best-effort datatype casting --- sql/base/base.sql | 44 ++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 44 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index 3c12cc3..355cf52 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -594,3 +594,47 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, return "$prefix$new_barcode$suffix"; $$ LANGUAGE PLPERL STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$ + DECLARE + attempt_value ALIAS FOR $1; + datatype ALIAS FOR $2; + fail_value ALIAS FOR $3; + output RECORD; + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output DATE; + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + -- 1.7.2.5