From 3c1e9fd23269849e32c73683a8457fb3095309e3 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 10 Jan 2018 17:13:29 -0500 Subject: [PATCH] Fix sample INSTR() functions in the plpgsql documentation. These functions are stated to be Oracle-compatible, but they weren't. Yugo Nagata noticed that while our code returns zero for a zero or negative fourth parameter (occur_index), Oracle throws an error. Further testing by me showed that there was also a discrepancy in the interpretation of a negative third parameter (beg_index): Oracle thinks that a negative beg_index indicates the last place where the target substring can *begin*, whereas our code thinks it is the last place where the target can *end*. Adjust the sample code to behave like Oracle in both these respects. Also change it to be a CDATA[] section, simplifying copying-and-pasting out of the documentation source file. And fix minor problems in the introductory comment, which wasn't very complete or accurate. Back-patch to all supported branches. Although this patch only touches documentation, we should probably call it out as a bug fix in the next minor release notes, since users who have adopted the functions will likely want to update their versions. Yugo Nagata and Tom Lane Discussion: https://postgr.es/m/20171229191705.c0b43a8c.nagata@sraoss.co.jp --- doc/src/sgml/plpgsql.sgml | 82 ++++++++++++++++++--------------------- 1 file changed, 38 insertions(+), 44 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 7d23ed437e..ddd054c6cc 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -5647,27 +5647,29 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; instr function - + 0 THEN temp_str := substring(string FROM beg_index); - pos := position(string_to_search IN temp_str); + pos := position(string_to_search_for IN temp_str); IF pos = 0 THEN RETURN 0; ELSE RETURN pos + beg_index - 1; END IF; - ELSIF beg_index < 0 THEN - ss_length := char_length(string_to_search); + ELSIF beg_index < 0 THEN + ss_length := char_length(string_to_search_for); length := char_length(string); - beg := length + beg_index - ss_length + 2; + beg := length + 1 + beg_index; - WHILE beg > 0 LOOP + 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 + IF string_to_search_for = temp_str THEN RETURN beg; END IF; @@ -5709,7 +5709,7 @@ END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; -CREATE FUNCTION instr(string varchar, string_to_search varchar, +CREATE FUNCTION instr(string varchar, string_to_search_for varchar, beg_index integer, occur_index integer) RETURNS integer AS $$ DECLARE @@ -5721,39 +5721,32 @@ DECLARE length integer; ss_length integer; BEGIN - IF beg_index > 0 THEN - beg := beg_index; - temp_str := substring(string FROM beg_index); + IF occur_index <= 0 THEN + RAISE 'argument ''%'' is out of range', occur_index + USING ERRCODE = '22003'; + END IF; + IF beg_index > 0 THEN + beg := beg_index - 1; 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); + pos := position(string_to_search_for IN temp_str); + IF pos = 0 THEN + RETURN 0; + END IF; + beg := beg + pos; END LOOP; - IF pos = 0 THEN - RETURN 0; - ELSE - RETURN beg; - END IF; - ELSIF beg_index < 0 THEN - ss_length := char_length(string_to_search); + RETURN beg; + ELSIF beg_index < 0 THEN + ss_length := char_length(string_to_search_for); length := char_length(string); - beg := length + beg_index - ss_length + 2; + beg := length + 1 + beg_index; - WHILE beg > 0 LOOP + 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 + IF string_to_search_for = temp_str THEN occur_number := occur_number + 1; - IF occur_number = occur_index THEN RETURN beg; END IF; @@ -5768,6 +5761,7 @@ BEGIN END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; +]]> -- 2.40.0