PostgreSQL仿照Oracle的instr函数

PostgreSQL模仿Oracle的instr函数

--  
-- instr functions that mimic Oracle's counterpart  
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.  
--  
-- Searches string1 beginning at the nth character for the mth occurrence  
-- of string2.  If n is negative, search backwards.  If m is not passed,  
-- assume 1 (search starts at first character).  
--  
  
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$  
DECLARE  
integer;  
BEGIN  
    pos:= instr($1, $2, 1);  
RETURN pos;  
END;  
$$ LANGUAGE plpgsql STRICT IMMUTABLE;  
  
  
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)  
RETURNS integer AS $$  
DECLARE  
integer NOT NULL DEFAULT 0;  
varchar;  
integer;  
integer;  
integer;  
BEGIN  
THEN  
substring(string FROM beg_index);  
IN temp_str);  
  
THEN  
RETURN 0;  
ELSE  
RETURN pos + beg_index - 1;  
END IF;  
ELSE  
        ss_length := char_length(string_to_search);  
        length := char_length(string);  
        beg := length + beg_index - ss_length + 2;  
  
        WHILE beg > 0 LOOP  
substring(string FROM beg FOR ss_length);  
IN temp_str);  
  
THEN  
RETURN beg;  
END IF;  
  
            beg := beg - 1;  
END LOOP;  
  
RETURN 0;  
END IF;  
END;  
$$ LANGUAGE plpgsql STRICT IMMUTABLE;  
  
  
CREATE FUNCTION instr(string varchar, string_to_search varchar,  
integer, occur_index integer)  
RETURNS integer AS $$  
DECLARE  
integer NOT NULL DEFAULT 0;  
integer NOT NULL DEFAULT 0;  
varchar;  
integer;  
integer;  
integer;  
integer;  
BEGIN  
THEN  
        beg := beg_index;  
substring(string FROM beg_index);  
  
FOR i IN 1..occur_index LOOP  
IN temp_str);  
  
THEN  
                beg := beg + pos - 1;  
ELSE  
                beg := beg + pos;  
END IF;  
  
substring(string FROM beg + 1);  
END LOOP;  
  
THEN  
RETURN 0;  
ELSE  
RETURN beg;  
END IF;  
ELSE  
        ss_length := char_length(string_to_search);  
        length := char_length(string);  
        beg := length + beg_index - ss_length + 2;  
  
        WHILE beg > 0 LOOP  
substring(string FROM beg FOR ss_length);  
IN temp_str);  
  
THEN  
                occur_number := occur_number + 1;  
  
THEN  
RETURN beg;  
END IF;  
END IF;  
  
            beg := beg - 1;  
END LOOP;  
  
RETURN 0;  
END IF;  
END;  
$$ LANGUAGE plpgsql STRICT IMMUTABLE;