joshlk/postgresql.md

## postgresql.md

      
    Raw
  

              postgresql.md
            
          
    Force cast conversion to integer or output null:
CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS BIGINT AS $$
DECLARE v_int_value BIGINT DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::BIGINT;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;
Floats:
CREATE OR REPLACE FUNCTION convert_to_float(v_input text)
RETURNS FLOAT AS $$
DECLARE v_int_value FLOAT DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::FLOAT;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid float value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;