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;  

1 comment:

Anonymous said...

An expert Oracle Sales Cloud consultant aligns CRM architecture with real-world sales operations to drive revenue velocity. By optimizing lead-to-opportunity workflows, configuring CPQ rules, and integrating real-time predictive analytics, they eliminate pipeline friction, empower sales reps, and deliver actionable pipeline forecasting across the enterprise.