Oracle porting sample instr function
A web site doc comment from user skong today points out a small issue
around the sample INSTR function given in plpgsql-porting.html that I
can't confirm (none of those dirty Oracle instances here today), but it
sounds legit.
A look at Oracle's documentation on the INSTR function at
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm
says that the 3rd input, position to start searching, cannot be zero.
skong says that Oracle will just return a 0 if you give it that invalid
input.
The INSTR implementation in the docs will instead search backwards from
the end of the string if you tell it to start at 0, same as if you gave
it a negative input. I think it's therefore possible to get the plpgsql
version to return a value in cases Oracle would instead return 0. Seems
like a straightforward thing to confirm and change the sample to do
differently; just have to add an explicit test for a 0 value of beg_index.
Greg Smith wrote:
A web site doc comment from user skong today points out a small issue
around the sample INSTR function given in plpgsql-porting.html that I
can't confirm (none of those dirty Oracle instances here today), but
it
sounds legit.
A look at Oracle's documentation on the INSTR function at
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.h
tm
says that the 3rd input, position to start searching, cannot be zero.
skong says that Oracle will just return a 0 if you give it that
invalid
input.
The INSTR implementation in the docs will instead search backwards
from
the end of the string if you tell it to start at 0, same as if you
gave
it a negative input. I think it's therefore possible to get the
plpgsql
version to return a value in cases Oracle would instead return 0.
Seems
like a straightforward thing to confirm and change the sample to do
differently; just have to add an explicit test for a 0 value of
beg_index.
I can confirm that Oracle returns 0 if the third argument to
INSTR is 0.
Yours,
Laurenz Albe
On Tue, Jul 3, 2012 at 8:42 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
I can confirm that Oracle returns 0 if the third argument to
INSTR is 0.
Can someone provide a suitable doc patch?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
I can confirm that Oracle returns 0 if the third argument to
INSTR is 0.
Can someone provide a suitable doc patch?
Here you are.
Yours,
Laurenz Albe
Attachments:
instr-doc.patchapplication/octet-stream; name=instr-doc.patchDownload
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index ba2c57b..4840f6e
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** BEGIN
*** 5115,5121 ****
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;
--- 5115,5121 ----
ELSE
RETURN pos + beg_index - 1;
END IF;
! ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
*************** BEGIN
*** 5132,5137 ****
--- 5132,5139 ----
END LOOP;
RETURN 0;
+ ELSE
+ RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
*************** BEGIN
*** 5170,5176 ****
ELSE
RETURN beg;
END IF;
! ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
--- 5172,5178 ----
ELSE
RETURN beg;
END IF;
! ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
*************** BEGIN
*** 5191,5196 ****
--- 5193,5200 ----
END LOOP;
RETURN 0;
+ ELSE
+ RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
On Wed, Jul 4, 2012 at 3:50 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Robert Haas wrote:
I can confirm that Oracle returns 0 if the third argument to
INSTR is 0.Can someone provide a suitable doc patch?
Thanks, committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Jul 2, 2012 at 8:51 PM, Greg Smith <greg@2ndquadrant.com> wrote:
<snip>
The INSTR implementation in the docs will instead search backwards from the
end of the string if you tell it to start at 0, same as if you gave it a
negative input. I think it's therefore possible to get the plpgsql version
to return a value in cases Oracle would instead return 0. Seems like a
straightforward thing to confirm and change the sample to do differently;
just have to add an explicit test for a 0 value of beg_index.
I wrote that sample eons ago with the plpgsql-porting doc. I probably
overlooked the 0 behavior. Thanks for reporting Greg, and thanks Albe
for providing a patch.
Roberto