CREATE OR REPLACE FUNCTION MIG_EXT.MIG_CLOB_COMPARE
/* The function is used to compare two clob strings.
   If both are matching, it returns 0.
   If string1 < string2, it returns -1
   If string1 > string2, it returns 1
*/
    ( lob_1                  IN 	CLOB
    , lob_2                  IN 	CLOB
    , amount                 IN 	INTEGER        := NULL
    , offset_1               IN 	INTEGER        := 1
    , offset_2               IN 	INTEGER        := 1 ) 
RETURNS INTEGER 
IMMUTABLE
AS
$$
DECLARE v_compare_res           INTEGER;
        v_compare_str1          TEXT;
        v_compare_str2          TEXT;
BEGIN

	/* if amount is empty, the strings to be considered for comparion are 
	   the complete strings from offset_1/offset_2.	*/
    IF amount IS NULL THEN
        v_compare_str1               := SUBSTR(lob_1::TEXT, offset_1);
        v_compare_str2               := SUBSTR(lob_2::TEXT, offset_2);
    ELSE
        v_compare_str1               := SUBSTR(lob_1::TEXT, offset_1, amount);
        v_compare_str2               := SUBSTR(lob_2::TEXT, offset_2, amount);
    END IF;

    v_compare_res := CASE WHEN v_compare_str1 = v_compare_str2 THEN 0
                          WHEN v_compare_str1 < v_compare_str2 THEN -1
                          WHEN v_compare_str1 > v_compare_str2 THEN 1
                     END;

    RETURN v_compare_res; 

END;
$$ 
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_EXT.regexp_find_replace_str
/* This function is used to find a string which does not exist in the input string. */
	( i_source_string   		IN  	TEXT ) 
RETURNS TEXT
AS
$$
DECLARE
	 l_replace_string   				TEXT	:= '#@!mig_repl_str$%^';
	 l_instr_pos    					INT;
BEGIN

	LOOP
		l_instr_pos 	:= STRPOS(i_source_string, l_replace_string);
		EXIT WHEN l_instr_pos IS NULL OR l_instr_pos = 0;
		
		l_replace_string := MD5(RANDOM()::TEXT);
	END LOOP;

	RETURN l_replace_string;
END;
$$ 
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_EXT.regexp_instr
/* This function is used to replicate the behaviour of Oracle REGEXP_INSTR. */
	( i_source_string   		IN  	TEXT
	, i_search_pattern   		IN  	TEXT
	, i_search_position  		IN  	FLOAT			:= 1
	, i_search_occurrence   	IN  	FLOAT			:= 1
	, i_return_option   		IN  	FLOAT 			:= 0
	, i_match_parameter	    	IN  	CHAR(1) 		:= 'c'
	) 
RETURNS INT
AS
$$
DECLARE
	 l_instr_pos    					INT;
	 l_final_instr_pos    				INT 	:= 0;
	 l_replace_string   				TEXT	:= '#@!mig_repl_str$%^';
	 l_replace_string_length    		INT;
	 l_process_string_length 			INT;
	 l_string_to_be_processed   		TEXT;
	 l_control_pos 						INT;
BEGIN

	/* To match with Oracle behaviour, decimal got truncated */
	i_search_position 	:= FLOOR(i_search_position);
	i_search_occurrence := FLOOR(i_search_occurrence);
	i_return_option		:= FLOOR(i_return_option);
	
	/* Validating the input value for i_search_position */
	IF i_search_position < 1
	-- if invalid value is passed for i_search_position
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_position (3th) parameter.', i_search_position;
	END IF;

	/* Validating the input value for i_search_occurrence */
	IF i_search_occurrence < 1
	-- if invalid value is passed for i_search_occurrence
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_occurrence (4th) parameter.', i_search_occurrence;
	END IF;

	/* Validating the input value for i_return_option */
	IF i_return_option < 0
	-- if invalid value is passed for i_return_option
	THEN
		RAISE EXCEPTION 'Argument % is out of range for return_option (5th) parameter.', i_return_option;
	ELSIF i_return_option > 0
	THEN	
	-- Supported valid value is 0 for i_return_option
	-- if unsupported valid value (i.e., greater than 0) is passed for i_return_option
		RAISE EXCEPTION '% is an unsupported return_option (5th) parameter.', i_return_option;
	END IF;
	
	/* Validating the input value for i_match_parameter */
	IF i_match_parameter IN ( 'i', 'c' )
	-- if supported valid value is passed for i_match_parameter
	THEN
		NULL;
	ELSIF i_match_parameter IN ( 'n', 'm', 'x' )
	-- if other valid value is passed for i_match_parameter
	THEN
		RAISE EXCEPTION '% is an unsupported match_parameter (6th parameter).', i_match_parameter;
	ELSIF i_match_parameter IS NULL
	THEN
	    i_match_parameter = 'c';
	ELSE 
	-- if invalid value is passed for i_match_parameter
		RAISE EXCEPTION '% is an invalid match_parameter (6th parameter).', i_match_parameter;
	END IF;

	IF    i_source_string     IS NULL 
	   OR i_search_pattern    IS NULL
	   OR i_search_position   IS NULL
	   OR i_search_occurrence IS NULL
	   OR i_return_option     IS NULL
	THEN
		RETURN NULL;
	END IF;

	-- finding the replacement string which does not exist in the input string
	l_string_to_be_processed    := SUBSTR(i_source_string, i_search_position);
	l_replace_string 			:= MIG_EXT.regexp_find_replace_str(l_string_to_be_processed);

	/* If all the input values are valid, below logic to find the instr */
	-- Find the string length of l_replace_string
	l_replace_string_length     := LENGTH(l_replace_string);

	-- identify the string to be processed based on "search position" by moving the control to "search position"
	l_control_pos				:= i_search_position;

	FOR ocr IN 1..i_search_occurrence
	LOOP
		-- identify the string to be processed based on "search position" (if first time)
		-- identify the string to be processed by moving the control after this occurrence
		l_string_to_be_processed    := SUBSTR(i_source_string, l_control_pos);
		l_process_string_length 	:= LENGTH(l_string_to_be_processed);
		
		-- replace the first occurrence of the pattern "i_search_pattern" replaced with the string "l_replace_string".
		l_string_to_be_processed    := REGEXP_REPLACE(l_string_to_be_processed, i_search_pattern, l_replace_string, i_match_parameter);

		-- find the position of the "l_replace_string" string
	    l_instr_pos 				:= STRPOS(l_string_to_be_processed, l_replace_string);

	    -- Exit if no match
	    IF l_instr_pos = 0 OR l_instr_pos IS NULL
		THEN
			l_final_instr_pos 	:= 0;
			EXIT;
		END IF;
		
		-- If match, find the final instr position
		l_final_instr_pos 	:= l_control_pos + l_instr_pos - 1;
		
		-- control position =  (final instr position) + length of (string found based on the pattern)
		l_control_pos 		:= l_final_instr_pos + (l_process_string_length - LENGTH(l_string_to_be_processed) + l_replace_string_length);
		
	END LOOP;
	
	RETURN l_final_instr_pos;
END;
$$ 
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_EXT.regexp_replace
/* This function is used to replicate the behaviour of Oracle REGEXP_REPLACE. */
	( i_source_string   		IN  	TEXT
	, i_search_pattern   		IN  	TEXT
	, i_replace_string   		IN  	TEXT 			:= NULL
	, i_search_position  		IN  	FLOAT			:= 1
	, i_search_occurrence   	IN  	FLOAT			:= 0
	, i_match_parameter	    	IN  	CHAR(1) 		:= 'c'
	) 
RETURNS TEXT
AS
$$
DECLARE
	 l_instr_pos    					INT;
	 l_string_to_be_processed 			TEXT;
	 l_target_string 					TEXT;
	 l_replace_start_pos    			INT;
	 l_match_parameter 					CHAR(2);
BEGIN

	/* To match with Oracle behaviour, decimal got truncated */
	i_search_position 	:= FLOOR(i_search_position);
	i_search_occurrence := FLOOR(i_search_occurrence);
	
	/* Validating the input value for i_search_position */
	IF i_search_position < 1
	-- if invalid value is passed for i_search_position
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_position (4th) parameter.', i_search_position;
	END IF;

	/* Validating the input value for i_search_occurrence */
	IF i_search_occurrence < 0
	-- if invalid value is passed for i_search_occurrence
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_occurrence (5th) parameter.', i_search_occurrence;
	END IF;

	/* Validating the input value for i_match_parameter */
	IF i_match_parameter IN ( 'i', 'c' )
	-- if supported valid value is passed for i_match_parameter
	THEN
		NULL;
	ELSIF i_match_parameter IN ( 'n', 'm', 'x' )
	-- if other valid value is passed for i_match_parameter
	THEN
		RAISE EXCEPTION '% is an unsupported match_parameter (6th parameter).', i_match_parameter;
	ELSIF i_match_parameter IS NULL
	THEN
	    i_match_parameter = 'c';
	ELSE 
	-- if invalid value is passed for i_match_parameter
		RAISE EXCEPTION '% is an invalid match_parameter (6th parameter).', i_match_parameter;
	END IF;

	IF    i_source_string     IS NULL 
	   OR i_search_pattern    IS NULL
	THEN
		RETURN i_source_string;
	END IF;
	
	IF    i_search_position   IS NULL
	   OR i_search_occurrence IS NULL
	THEN
		RETURN NULL;
	END IF;
	
	IF i_search_occurrence = 0
	-- if i_search_occurrence is 0, replace all the occurrences.
	THEN
		l_replace_start_pos			:= i_search_position;
		l_match_parameter			:= i_match_parameter || 'g';
	ELSE
	-- if it is not 0, replace only a specific occurrence.
		l_instr_pos 		:= MIG_EXT.regexp_instr	( i_source_string, i_search_pattern
														, i_search_position, i_search_occurrence   
														, 0, i_match_parameter	    
														);
	
		l_replace_start_pos := l_instr_pos;
		l_match_parameter	:= i_match_parameter;

	END IF;
	
	IF l_replace_start_pos IS NULL OR l_replace_start_pos = 0
	THEN
		l_target_string 	:= i_source_string;
	ELSE
		l_string_to_be_processed 	:= SUBSTR(i_source_string, l_replace_start_pos);
		l_target_string 			:= SUBSTR(i_source_string, 1, l_replace_start_pos - 1)
										|| REGEXP_REPLACE(l_string_to_be_processed, i_search_pattern, i_replace_string, l_match_parameter);		
	END IF;
	
	RETURN l_target_string;
END;
$$ 
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_EXT.regexp_substr
/* This function is used to replicate the behaviour of Oracle REGEXP_SUBSTR. */
	( i_source_string   		IN  	TEXT
	, i_search_pattern   		IN  	TEXT
	, i_search_position  		IN  	FLOAT			:= 1
	, i_search_occurrence   	IN  	FLOAT			:= 1
	, i_match_parameter	    	IN  	CHAR(1) 		:= 'c'
	) 
RETURNS TEXT
AS
$$
DECLARE
	 l_instr_pos    					INT;
	 l_string_to_be_processed 			TEXT;
	 l_replace_string   				TEXT	:= '#@!mig_repl_str$%^';
	 l_replaced_string 		 			TEXT;
	 l_substring 						TEXT;
BEGIN

	/* To match with Oracle behaviour, decimal got truncated */
	i_search_position 	:= FLOOR(i_search_position);
	i_search_occurrence := FLOOR(i_search_occurrence);
	
	/* Validating the input value for i_search_position */
	IF i_search_position < 1
	-- if invalid value is passed for i_search_position
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_position (3th) parameter.', i_search_position;
	END IF;

	/* Validating the input value for i_search_occurrence */
	IF i_search_occurrence < 1
	-- if invalid value is passed for i_search_occurrence
	THEN
		RAISE EXCEPTION 'Argument % is out of range for search_occurrence (4th) parameter.', i_search_occurrence;
	END IF;

	/* Validating the input value for i_match_parameter */
	IF i_match_parameter IN ( 'i', 'c' )
	-- if supported valid value is passed for i_match_parameter
	THEN
		NULL;
	ELSIF i_match_parameter IN ( 'n', 'm', 'x' )
	-- if other valid value is passed for i_match_parameter
	THEN
		RAISE EXCEPTION '% is an unsupported match_parameter (5th parameter).', i_match_parameter;
	ELSIF i_match_parameter IS NULL
	THEN
	    i_match_parameter = 'c';
	ELSE 
	-- if invalid value is passed for i_match_parameter
		RAISE EXCEPTION '% is an invalid match_parameter (5th parameter).', i_match_parameter;
	END IF;

	IF    i_source_string     IS NULL 
	   OR i_search_pattern    IS NULL
	   OR i_search_position   IS NULL
	   OR i_search_occurrence IS NULL
	THEN
		RETURN NULL;
	END IF;

	l_instr_pos 		:= MIG_EXT.regexp_instr	( i_source_string, i_search_pattern
													, i_search_position, i_search_occurrence   
													, 0, i_match_parameter	    
													);
	
	IF l_instr_pos IS NULL OR l_instr_pos = 0
	THEN
		l_substring 	:= NULL;
	ELSE
		l_string_to_be_processed 	:= SUBSTR(i_source_string, l_instr_pos);
	
		l_replace_string 			:= MIG_EXT.regexp_find_replace_str(l_string_to_be_processed);
		l_replaced_string 			:= REGEXP_REPLACE(l_string_to_be_processed, i_search_pattern, l_replace_string, i_match_parameter);	
		
		l_substring 				:= SUBSTR( l_string_to_be_processed, 1, (LENGTH(l_string_to_be_processed) - (LENGTH(l_replaced_string) - LENGTH(l_replace_string))) );
	END IF;
	
	RETURN l_substring;
END;
$$ 
LANGUAGE plpgsql;

