Disclaimer

These scripts come without warranty of any kind. Use them at your own risk. I assume no liability for the accuracy, correctness, completeness, or usefulness of any information provided by this site nor for any sort of damages using these scripts may cause.

Saturday, October 21, 2023

PostgreSQL function similar to Oracle validate_conversion function

 

This function accepts a text value, and a data type; returns 1 if the text value can be converted to the data type, otherwise 0.   This is tested on  server 15.4


CREATE OR REPLACE FUNCTION validate_conversion (p_input TEXT, p_type VARCHAR(128))
RETURNS integer
AS
$body$
    DECLARE
           valid_convert integer = 1;
           tmp_val TEXT;
               
     BEGIN 
         p_type = TRIM(LOWER(p_type));
               
         EXECUTE FORMAT('SELECT CAST (''%s'' AS %s)', p_input, p_type)
         INTO tmp_val;
           
               IF LENGTH(tmp_val) <> LENGTH(p_input)
               THEN
                       valid_convert = 0;
               END IF;
               RETURN valid_convert;
                          
               EXCEPTION
                       WHEN OTHERS THEN
                               valid_convert = 0;
                               RETURN valid_convert;
END;
$body$
LANGUAGE plpgsql;