Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit08adf68

Browse files
committed
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 ornegative fourth parameter (occur_index), Oracle throws an error.Further testing by me showed that there was also a discrepancy in theinterpretation of a negative third parameter (beg_index): Oracle thinksthat a negative beg_index indicates the last place where the targetsubstring can *begin*, whereas our code thinks it is the last placewhere 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-pastingout of the documentation source file. And fix minor problems in theintroductory comment, which wasn't very complete or accurate.Back-patch to all supported branches. Although this patch only touchesdocumentation, we should probably call it out as a bug fix in the nextminor release notes, since users who have adopted the functions willlikely want to update their versions.Yugo Nagata and Tom LaneDiscussion:https://postgr.es/m/20171229191705.c0b43a8c.nagata@sraoss.co.jp
1 parent7eb0187 commit08adf68

File tree

1 file changed

+38
-44
lines changed

1 file changed

+38
-44
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 38 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -5634,27 +5634,29 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
56345634
<primary><function>instr</> function</primary>
56355635
</indexterm>
56365636

5637-
<programlisting>
5637+
<programlisting><![CDATA[
56385638
--
56395639
-- instr functions that mimic Oracle's counterpart
5640-
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
5640+
-- Syntax: instr(string1, string2 [, n [, m]])
5641+
-- where [] denotes optional parameters.
56415642
--
5642-
-- Searches string1 beginning at the nth character for the mth occurrence
5643-
-- of string2. If n is negative, search backwards. If m is not passed,
5644-
-- assume 1 (search starts at first character).
5643+
-- Search string1, beginning at the nth character, for the mth occurrence
5644+
-- of string2. If n is negative, search backwards, starting at the abs(n)'th
5645+
-- character from the end of string1.
5646+
-- If n is not passed, assume 1 (search starts at first character).
5647+
-- If m is not passed, assume 1 (find first occurrence).
5648+
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
56455649
--
56465650

56475651
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
5648-
DECLARE
5649-
pos integer;
56505652
BEGIN
5651-
pos:= instr($1, $2, 1);
5652-
RETURN pos;
5653+
RETURN instr($1, $2, 1);
56535654
END;
56545655
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
56555656

56565657

5657-
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
5658+
CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
5659+
beg_index integer)
56585660
RETURNS integer AS $$
56595661
DECLARE
56605662
pos integer NOT NULL DEFAULT 0;
@@ -5663,25 +5665,23 @@ DECLARE
56635665
length integer;
56645666
ss_length integer;
56655667
BEGIN
5666-
IF beg_index&gt; 0 THEN
5668+
IF beg_index> 0 THEN
56675669
temp_str := substring(string FROM beg_index);
5668-
pos := position(string_to_search IN temp_str);
5670+
pos := position(string_to_search_for IN temp_str);
56695671

56705672
IF pos = 0 THEN
56715673
RETURN 0;
56725674
ELSE
56735675
RETURN pos + beg_index - 1;
56745676
END IF;
5675-
ELSIF beg_index&lt; 0 THEN
5676-
ss_length := char_length(string_to_search);
5677+
ELSIF beg_index< 0 THEN
5678+
ss_length := char_length(string_to_search_for);
56775679
length := char_length(string);
5678-
beg := length +beg_index - ss_length + 2;
5680+
beg := length +1 + beg_index;
56795681

5680-
WHILE beg&gt; 0 LOOP
5682+
WHILE beg> 0 LOOP
56815683
temp_str := substring(string FROM beg FOR ss_length);
5682-
pos := position(string_to_search IN temp_str);
5683-
5684-
IF pos &gt; 0 THEN
5684+
IF string_to_search_for = temp_str THEN
56855685
RETURN beg;
56865686
END IF;
56875687

@@ -5696,7 +5696,7 @@ END;
56965696
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
56975697

56985698

5699-
CREATE FUNCTION instr(string varchar,string_to_search varchar,
5699+
CREATE FUNCTION instr(string varchar,string_to_search_for varchar,
57005700
beg_index integer, occur_index integer)
57015701
RETURNS integer AS $$
57025702
DECLARE
@@ -5708,39 +5708,32 @@ DECLARE
57085708
length integer;
57095709
ss_length integer;
57105710
BEGIN
5711-
IF beg_index &gt; 0 THEN
5712-
beg := beg_index;
5713-
temp_str := substring(string FROM beg_index);
5711+
IF occur_index <= 0 THEN
5712+
RAISE 'argument ''%'' is out of range', occur_index
5713+
USING ERRCODE = '22003';
5714+
END IF;
57145715

5716+
IF beg_index > 0 THEN
5717+
beg := beg_index - 1;
57155718
FOR i IN 1..occur_index LOOP
5716-
pos := position(string_to_search IN temp_str);
5717-
5718-
IF i = 1 THEN
5719-
beg := beg + pos - 1;
5720-
ELSE
5721-
beg := beg + pos;
5722-
END IF;
5723-
57245719
temp_str := substring(string FROM beg + 1);
5720+
pos := position(string_to_search_for IN temp_str);
5721+
IF pos = 0 THEN
5722+
RETURN 0;
5723+
END IF;
5724+
beg := beg + pos;
57255725
END LOOP;
57265726

5727-
IF pos = 0 THEN
5728-
RETURN 0;
5729-
ELSE
5730-
RETURN beg;
5731-
END IF;
5732-
ELSIF beg_index &lt; 0 THEN
5733-
ss_length := char_length(string_to_search);
5727+
RETURN beg;
5728+
ELSIF beg_index < 0 THEN
5729+
ss_length := char_length(string_to_search_for);
57345730
length := char_length(string);
5735-
beg := length +beg_index - ss_length + 2;
5731+
beg := length +1 + beg_index;
57365732

5737-
WHILE beg&gt; 0 LOOP
5733+
WHILE beg> 0 LOOP
57385734
temp_str := substring(string FROM beg FOR ss_length);
5739-
pos := position(string_to_search IN temp_str);
5740-
5741-
IF pos &gt; 0 THEN
5735+
IF string_to_search_for = temp_str THEN
57425736
occur_number := occur_number + 1;
5743-
57445737
IF occur_number = occur_index THEN
57455738
RETURN beg;
57465739
END IF;
@@ -5755,6 +5748,7 @@ BEGIN
57555748
END IF;
57565749
END;
57575750
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
5751+
]]>
57585752
</programlisting>
57595753
</sect2>
57605754

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp