From 62ae8fc257b7637ba3d378ca21ff61a674bf2553 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 26 Dec 2012 12:00:32 -0500 Subject: [PATCH] rework attempt_cast function This function now returns the original value if it can be cast to the desired type, and NULL otherwise. For example: UPDATE foo SET price = migration_tools.attempt_cast(l_price, 'NUMERIC(8,2)')::NUMERIC(8,2); Signed-off-by: Galen Charlton --- sql/base/base.sql | 21 +++++++-------------- 1 files changed, 7 insertions(+), 14 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index f0b5654..e18c004 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -624,25 +624,18 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, return "$prefix$new_barcode$suffix"; $$ LANGUAGE PLPERLU STABLE; -CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$ +-- remove previous version of this function +DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT); + +CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT 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; + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'; + RETURN attempt_value; EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;' - LOOP - RETURN output; - END LOOP; + WHEN OTHERS THEN RETURN NULL; END; $$ LANGUAGE PLPGSQL STRICT STABLE; -- 1.7.2.5