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

Commit1226051

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 parentc24a908 commit1226051

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
@@ -5561,27 +5561,29 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
55615561
<primary><function>instr</> function</primary>
55625562
</indexterm>
55635563

5564-
<programlisting>
5564+
<programlisting><![CDATA[
55655565
--
55665566
-- instr functions that mimic Oracle's counterpart
5567-
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
5567+
-- Syntax: instr(string1, string2 [, n [, m]])
5568+
-- where [] denotes optional parameters.
55685569
--
5569-
-- Searches string1 beginning at the nth character for the mth occurrence
5570-
-- of string2. If n is negative, search backwards. If m is not passed,
5571-
-- assume 1 (search starts at first character).
5570+
-- Search string1, beginning at the nth character, for the mth occurrence
5571+
-- of string2. If n is negative, search backwards, starting at the abs(n)'th
5572+
-- character from the end of string1.
5573+
-- If n is not passed, assume 1 (search starts at first character).
5574+
-- If m is not passed, assume 1 (find first occurrence).
5575+
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
55725576
--
55735577

55745578
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
5575-
DECLARE
5576-
pos integer;
55775579
BEGIN
5578-
pos:= instr($1, $2, 1);
5579-
RETURN pos;
5580+
RETURN instr($1, $2, 1);
55805581
END;
55815582
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
55825583

55835584

5584-
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
5585+
CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
5586+
beg_index integer)
55855587
RETURNS integer AS $$
55865588
DECLARE
55875589
pos integer NOT NULL DEFAULT 0;
@@ -5590,25 +5592,23 @@ DECLARE
55905592
length integer;
55915593
ss_length integer;
55925594
BEGIN
5593-
IF beg_index&gt; 0 THEN
5595+
IF beg_index> 0 THEN
55945596
temp_str := substring(string FROM beg_index);
5595-
pos := position(string_to_search IN temp_str);
5597+
pos := position(string_to_search_for IN temp_str);
55965598

55975599
IF pos = 0 THEN
55985600
RETURN 0;
55995601
ELSE
56005602
RETURN pos + beg_index - 1;
56015603
END IF;
5602-
ELSIF beg_index&lt; 0 THEN
5603-
ss_length := char_length(string_to_search);
5604+
ELSIF beg_index< 0 THEN
5605+
ss_length := char_length(string_to_search_for);
56045606
length := char_length(string);
5605-
beg := length +beg_index - ss_length + 2;
5607+
beg := length +1 + beg_index;
56065608

5607-
WHILE beg&gt; 0 LOOP
5609+
WHILE beg> 0 LOOP
56085610
temp_str := substring(string FROM beg FOR ss_length);
5609-
pos := position(string_to_search IN temp_str);
5610-
5611-
IF pos &gt; 0 THEN
5611+
IF string_to_search_for = temp_str THEN
56125612
RETURN beg;
56135613
END IF;
56145614

@@ -5623,7 +5623,7 @@ END;
56235623
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
56245624

56255625

5626-
CREATE FUNCTION instr(string varchar,string_to_search varchar,
5626+
CREATE FUNCTION instr(string varchar,string_to_search_for varchar,
56275627
beg_index integer, occur_index integer)
56285628
RETURNS integer AS $$
56295629
DECLARE
@@ -5635,39 +5635,32 @@ DECLARE
56355635
length integer;
56365636
ss_length integer;
56375637
BEGIN
5638-
IF beg_index &gt; 0 THEN
5639-
beg := beg_index;
5640-
temp_str := substring(string FROM beg_index);
5638+
IF occur_index <= 0 THEN
5639+
RAISE 'argument ''%'' is out of range', occur_index
5640+
USING ERRCODE = '22003';
5641+
END IF;
56415642

5643+
IF beg_index > 0 THEN
5644+
beg := beg_index - 1;
56425645
FOR i IN 1..occur_index LOOP
5643-
pos := position(string_to_search IN temp_str);
5644-
5645-
IF i = 1 THEN
5646-
beg := beg + pos - 1;
5647-
ELSE
5648-
beg := beg + pos;
5649-
END IF;
5650-
56515646
temp_str := substring(string FROM beg + 1);
5647+
pos := position(string_to_search_for IN temp_str);
5648+
IF pos = 0 THEN
5649+
RETURN 0;
5650+
END IF;
5651+
beg := beg + pos;
56525652
END LOOP;
56535653

5654-
IF pos = 0 THEN
5655-
RETURN 0;
5656-
ELSE
5657-
RETURN beg;
5658-
END IF;
5659-
ELSIF beg_index &lt; 0 THEN
5660-
ss_length := char_length(string_to_search);
5654+
RETURN beg;
5655+
ELSIF beg_index < 0 THEN
5656+
ss_length := char_length(string_to_search_for);
56615657
length := char_length(string);
5662-
beg := length +beg_index - ss_length + 2;
5658+
beg := length +1 + beg_index;
56635659

5664-
WHILE beg&gt; 0 LOOP
5660+
WHILE beg> 0 LOOP
56655661
temp_str := substring(string FROM beg FOR ss_length);
5666-
pos := position(string_to_search IN temp_str);
5667-
5668-
IF pos &gt; 0 THEN
5662+
IF string_to_search_for = temp_str THEN
56695663
occur_number := occur_number + 1;
5670-
56715664
IF occur_number = occur_index THEN
56725665
RETURN beg;
56735666
END IF;
@@ -5682,6 +5675,7 @@ BEGIN
56825675
END IF;
56835676
END;
56845677
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
5678+
]]>
56855679
</programlisting>
56865680
</sect2>
56875681

0 commit comments

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