--
 -- Oracle のものと同じ動作をするinstr 関数
 -- 構文:instr(string1,string2,[n],[m])ただし、[]は省略可能なパラメータ
 --
 -- string1 内のn番目の文字からm番目の文字まででstring2 を探します。
 -- n が負の場合、逆方向に検索します。mが渡されなかった場合は、1 と
 -- みなします(最初の文字から検索を始めます)。
 --
 
 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS '
 DECLARE
     pos integer;
 BEGIN
     pos:= instr($1, $2, 1);
     RETURN pos;
 END;
 ' LANGUAGE plpgsql;
 
 
 CREATE FUNCTION instr(varchar, varchar, varchar) RETURNS integer AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     pos integer NOT NULL DEFAULT 0;
     temp_str varchar;
     beg integer;
     length integer;
     ss_length integer;
 BEGIN
     IF beg_index > 0 THEN
         temp_str := substring(string FROM beg_index);
         pos := position(string_to_search IN temp_str);
 
         IF pos = 0 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
             temp_str := substring(string FROM beg FOR ss_length);
             pos := position(string_to_search IN temp_str);
 
             IF pos > 0 THEN
                 RETURN beg;
             END IF;
 
             beg := beg - 1;
         END LOOP;
 
         RETURN 0;
     END IF;
 END;
 ' LANGUAGE plpgsql;
 CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     occur_index ALIAS FOR $4;
     pos integer NOT NULL DEFAULT 0;
     occur_number integer NOT NULL DEFAULT 0;
     temp_str varchar;
     beg integer;
     i integer;
     length integer;
     ss_length integer;
 BEGIN
     IF beg_index > 0 THEN
         beg := beg_index;
         temp_str := substring(string FROM beg_index);
 
         FOR i IN 1..occur_index LOOP
             pos := position(string_to_search IN temp_str);
 
             IF i = 1 THEN
                 beg := beg + pos - 1;
             ELSE
                 beg := beg + pos;
             END IF;
 
             temp_str := substring(string FROM beg + 1);
      END LOOP;
 
      IF pos = 0 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
             temp_str := substring(string FROM beg FOR ss_length);
             pos := position(string_to_search IN temp_str);
 
             IF pos > 0 THEN
                 occur_number := occur_number + 1;
 
                 IF occur_number = occur_index THEN
                     RETURN beg;
                 END IF;
             END IF;
 
             beg := beg - 1;
         END LOOP;
 
         RETURN 0;
       END IF;
 END;
 ' LANGUAGE plpgsql;