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

Commit3c1e9fd

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 parent70d6226 commit3c1e9fd

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
@@ -5647,27 +5647,29 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
56475647
<primary><function>instr</function> function</primary>
56485648
</indexterm>
56495649

5650-
<programlisting>
5650+
<programlisting><![CDATA[
56515651
--
56525652
-- instr functions that mimic Oracle's counterpart
5653-
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
5653+
-- Syntax: instr(string1, string2 [, n [, m]])
5654+
-- where [] denotes optional parameters.
56545655
--
5655-
-- Searches string1 beginning at the nth character for the mth occurrence
5656-
-- of string2. If n is negative, search backwards. If m is not passed,
5657-
-- assume 1 (search starts at first character).
5656+
-- Search string1, beginning at the nth character, for the mth occurrence
5657+
-- of string2. If n is negative, search backwards, starting at the abs(n)'th
5658+
-- character from the end of string1.
5659+
-- If n is not passed, assume 1 (search starts at first character).
5660+
-- If m is not passed, assume 1 (find first occurrence).
5661+
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
56585662
--
56595663

56605664
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
5661-
DECLARE
5662-
pos integer;
56635665
BEGIN
5664-
pos:= instr($1, $2, 1);
5665-
RETURN pos;
5666+
RETURN instr($1, $2, 1);
56665667
END;
56675668
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
56685669

56695670

5670-
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
5671+
CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
5672+
beg_index integer)
56715673
RETURNS integer AS $$
56725674
DECLARE
56735675
pos integer NOT NULL DEFAULT 0;
@@ -5676,25 +5678,23 @@ DECLARE
56765678
length integer;
56775679
ss_length integer;
56785680
BEGIN
5679-
IF beg_index&gt; 0 THEN
5681+
IF beg_index> 0 THEN
56805682
temp_str := substring(string FROM beg_index);
5681-
pos := position(string_to_search IN temp_str);
5683+
pos := position(string_to_search_for IN temp_str);
56825684

56835685
IF pos = 0 THEN
56845686
RETURN 0;
56855687
ELSE
56865688
RETURN pos + beg_index - 1;
56875689
END IF;
5688-
ELSIF beg_index&lt; 0 THEN
5689-
ss_length := char_length(string_to_search);
5690+
ELSIF beg_index< 0 THEN
5691+
ss_length := char_length(string_to_search_for);
56905692
length := char_length(string);
5691-
beg := length +beg_index - ss_length + 2;
5693+
beg := length +1 + beg_index;
56925694

5693-
WHILE beg&gt; 0 LOOP
5695+
WHILE beg> 0 LOOP
56945696
temp_str := substring(string FROM beg FOR ss_length);
5695-
pos := position(string_to_search IN temp_str);
5696-
5697-
IF pos &gt; 0 THEN
5697+
IF string_to_search_for = temp_str THEN
56985698
RETURN beg;
56995699
END IF;
57005700

@@ -5709,7 +5709,7 @@ END;
57095709
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
57105710

57115711

5712-
CREATE FUNCTION instr(string varchar,string_to_search varchar,
5712+
CREATE FUNCTION instr(string varchar,string_to_search_for varchar,
57135713
beg_index integer, occur_index integer)
57145714
RETURNS integer AS $$
57155715
DECLARE
@@ -5721,39 +5721,32 @@ DECLARE
57215721
length integer;
57225722
ss_length integer;
57235723
BEGIN
5724-
IF beg_index &gt; 0 THEN
5725-
beg := beg_index;
5726-
temp_str := substring(string FROM beg_index);
5724+
IF occur_index <= 0 THEN
5725+
RAISE 'argument ''%'' is out of range', occur_index
5726+
USING ERRCODE = '22003';
5727+
END IF;
57275728

5729+
IF beg_index > 0 THEN
5730+
beg := beg_index - 1;
57285731
FOR i IN 1..occur_index LOOP
5729-
pos := position(string_to_search IN temp_str);
5730-
5731-
IF i = 1 THEN
5732-
beg := beg + pos - 1;
5733-
ELSE
5734-
beg := beg + pos;
5735-
END IF;
5736-
57375732
temp_str := substring(string FROM beg + 1);
5733+
pos := position(string_to_search_for IN temp_str);
5734+
IF pos = 0 THEN
5735+
RETURN 0;
5736+
END IF;
5737+
beg := beg + pos;
57385738
END LOOP;
57395739

5740-
IF pos = 0 THEN
5741-
RETURN 0;
5742-
ELSE
5743-
RETURN beg;
5744-
END IF;
5745-
ELSIF beg_index &lt; 0 THEN
5746-
ss_length := char_length(string_to_search);
5740+
RETURN beg;
5741+
ELSIF beg_index < 0 THEN
5742+
ss_length := char_length(string_to_search_for);
57475743
length := char_length(string);
5748-
beg := length +beg_index - ss_length + 2;
5744+
beg := length +1 + beg_index;
57495745

5750-
WHILE beg&gt; 0 LOOP
5746+
WHILE beg> 0 LOOP
57515747
temp_str := substring(string FROM beg FOR ss_length);
5752-
pos := position(string_to_search IN temp_str);
5753-
5754-
IF pos &gt; 0 THEN
5748+
IF string_to_search_for = temp_str THEN
57555749
occur_number := occur_number + 1;
5756-
57575750
IF occur_number = occur_index THEN
57585751
RETURN beg;
57595752
END IF;
@@ -5768,6 +5761,7 @@ BEGIN
57685761
END IF;
57695762
END;
57705763
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
5764+
]]>
57715765
</programlisting>
57725766
</sect2>
57735767

0 commit comments

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