Redshift utility functions

redshift_safe_cast_functions.sql

-- Collection of functions to safely cast data from one type to another


-- Cast a VARCHAR to an INT and return NULL if it doesn't match accepted regexp
-- Prevents ETL dying due to data quality errors

-- Purpose: This UDF takes a VARCHAR argument,
-- and returns an INT, or NULL if regexp doesn't match accepted format for INT
-- Internal dependencies: None
-- External dependencies: None
-- 2019-16-19: written by Iain Gray
CREATE OR REPLACE FUNCTION f_sql_safe_cast_int(varchar)
 RETURNS INT
IMMUTABLE
AS $$
SELECT CASE
 WHEN TRIM($1) ~ '^[0-9]+$'
  THEN TRIM($1)
 ELSE NULL END::INT
$$
LANGUAGE SQL;

-- Purpose: This UDF takes a VARCHAR argument,
-- and returns a DATE, or NULL if if regexp doesn't match accepted format for DATE
-- NB DATE type is mutable so we can't use IMMUTABLE
-- Internal dependencies: None
-- External dependencies: None
-- 2019-16-19: written by Iain Gray
CREATE OR REPLACE FUNCTION f_sql_safe_cast_date(varchar)
 RETURNS DATE
STABLE
AS $$
SELECT CASE
 WHEN TRIM($1) ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}'
  THEN TRIM($1)
 ELSE NULL END::DATE
$$
LANGUAGE SQL;