Thread

1. Oracle porting sample instr function

   Greg Smith <greg@2ndquadrant.com> — 2012-07-03T00:51:14Z

   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.
   
   
   

2. Re: Oracle porting sample instr function

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-07-03T12:42:30Z

   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
   
   

3. Re: Oracle porting sample instr function

   Robert Haas <robertmhaas@gmail.com> — 2012-07-03T12:47:33Z

   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
   
   

4. Re: Oracle porting sample instr function

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-07-04T07:50:29Z

   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
   

5. Re: Oracle porting sample instr function

   Robert Haas <robertmhaas@gmail.com> — 2012-07-04T21:20:47Z

   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
   
   

6. Re: Oracle porting sample instr function

   Roberto Mello <roberto.mello@gmail.com> — 2012-07-06T11:04:33Z

   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