Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.236.160])
	by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2NEZpw71652
	for <pgsql-docs@postgresql.org>; Fri, 23 Mar 2001 09:35:55 -0500 (EST)
	(envelope-from roberto@brasileiro.net)
Received: (from roberto@localhost)
	by brasileiro.net (8.11.3/8.11.2/Debian 8.11.2-1) id f2NEZg211033
	for pgsql-docs@postgresql.org; Fri, 23 Mar 2001 07:35:42 -0700
Date: Fri, 23 Mar 2001 07:35:42 -0700
From: Roberto Mello <rmello@cc.usu.edu>
To: pgsql-docs@postgresql.org
Subject: plsql.sgml patch
Message-ID: <20010323073542.A11008@cc.usu.edu>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="dDRMvlgZJXvWKvBx"
Content-Disposition: inline
User-Agent: Mutt/1.3.15i
X-Archive-Number: 200103/117
X-Sequence-Number: 551

--dDRMvlgZJXvWKvBx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

	I sent a patch to plsql.sgml to Peter a four days ago and the
message is bouncing, so I am sending this to the list. The patch corrects 
a bug in my instr function and has the third instr function in PL/pgSQL 
instead of PL/Tcl.
	
	Thanks,

	-Roberto

----- Forwarded message from Mail Delivery Subsystem <MAILER-DAEMON> -----

From: Mail Delivery Subsystem <MAILER-DAEMON>
To: roberto
Subject: Returned mail: see transcript for details
Auto-Submitted: auto-generated (failure)

The original message was received at Mon, 19 Mar 2001 17:55:20 -0700
from roberto@localhost

   ----- The following addresses had permanent fatal errors -----
peter_e@gmx.net
    (reason: 451 4.4.1 peter_e@gmx.net... reply: read error from mx0.gmx.de.)

   ----- Transcript of session follows -----
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
... while talking to mx0.gmx.net.:
>>> QGJyYXNpbGVpcm8ubmV0IDVjY2EyMzBlZGU5NTY1ZGI2NDkzNmZhZjVkNTgwNGJl
<<< 535 {mx25} auth failure
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
>>> AUTH PLAIN AEBicmFzaWxlaXJvLm5ldAA=
<<< 535 {mx25} auth failure
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
451 4.4.1 peter_e@gmx.net... reply: read error from mx0.gmx.net.
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
... while talking to mx0.gmx.de.:
>>> QGJyYXNpbGVpcm8ubmV0IDQ0ZmUxY2NhYjZkMWM5M2EwYTY1ZGYzYzI3ODRkMjNm
<<< 535 {mx13} auth failure
>>> AUTH PLAIN AEBicmFzaWxlaXJvLm5ldAA=
<<< 535 {mx13} auth failure
451 4.0.0 readauth: cannot open /etc/mail/auth-info: No such file or directory
451 4.4.1 peter_e@gmx.net... reply: read error from mx0.gmx.de.
peter_e@gmx.net... Deferred: Connection reset by mx0.gmx.de.
Message could not be delivered for zero seconds
Message will be deleted from queue

Reporting-MTA: dns; brasileiro.net
Arrival-Date: Mon, 19 Mar 2001 17:55:20 -0700

Final-Recipient: RFC822; peter_e@gmx.net
Action: failed
Status: 4.4.7
Diagnostic-Code: SMTP; 451 4.4.1 peter_e@gmx.net... reply: read error from mx0.gmx.de.
Last-Attempt-Date: Mon, 19 Mar 2001 17:55:45 -0700

From: Roberto Mello <rmello@cc.usu.edu>
To: Peter Eisentraut <peter_e@gmx.net>
Subject: Patch to plsql.sgml

	Hi Peter,

	Robert Gaszewski made some modifications to my instr functions,
including porting the last function
(instr(varchar,varchar,integer,integer)) to PL/pgSQL (instead of PL/Tcl).
He also found a small bug on my first PG/pgSQL port of instr.

	I am attaching a patch, made against the current plsql.sgml from CVS.

	Thanks,

	-Roberto
----- End forwarded message -----

-- 
Computer Science			Utah State University
Space Dynamics Laboratory		Web Developer
USU Free Software & GNU/Linux Club 	http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto

--dDRMvlgZJXvWKvBx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="instr-plsql.diff"

*** plsql-orig.sgml	Mon Mar 19 17:21:26 2001
--- plsql-new.sgml	Mon Mar 19 17:39:20 2001
*************** WITH (isstrict, iscachable);
*** 2426,2427 ****
--- 2426,2428 ----
  -- by Roberto Mello (rmello@fslc.usu.edu)
+ -- modified by Robert Gaszewski <graszew@poland.com> 
  -- Licensed under the GPL v2 or later.
*************** WITH (isstrict, iscachable);
*** 2429,2432 ****
  
! DROP FUNCTION instr(varchar, varchar);
! CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS '
  DECLARE
--- 2430,2433 ----
  
! DROP FUNCTION instr(varchar,varchar);
! CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS '
  DECLARE
*************** DECLARE
*** 2434,2442 ****
  BEGIN
!     pos:= instr($1, $2, 1);
      RETURN pos;
  END;
! ' LANGUAGE 'plpgsql';
  
! DROP FUNCTION instr(varchar, varchar, integer);
! CREATE FUNCTION instr(varchar, varchar, integer) RETURNS integer AS '
  DECLARE
--- 2435,2491 ----
  BEGIN
!     pos:= instr($1,$2,1);
      RETURN pos;
  END;
! ' language 'plpgsql';
  
! 
! DROP FUNCTION instr(varchar,varchar,integer);
! CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS '
! DECLARE
!     string ALIAS FOR $1;
!     string_to_search ALIAS FOR $2;
!     beg_index ALIAS FOR $3;
!     pos integer NOT NULL DEFAULT 0;
!     temp_str varchar;
!     beg integer;
!     length integer;
!     ss_length integer;
! BEGIN
!     IF beg_index > 0 THEN
! 
!        temp_str := substring(string FROM beg_index);
!        pos := position(string_to_search IN temp_str);
! 
!        IF pos = 0 THEN
! 	         RETURN 0;
! 	     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;
! 
!        WHILE beg > 0 LOOP
! 
!            temp_str := substring(string FROM beg FOR ss_length);
! 	         pos := position(string_to_search IN temp_str);
! 
! 	         IF pos > 0 THEN
! 		           RETURN beg;
! 	         END IF;
! 
! 	         beg := beg - 1;
!        END LOOP;
!        RETURN 0;
!     END IF;
! END;
! ' language 'plpgsql';
! 
! --
! -- Written by Robert Gaszewski (graszew@poland.com)
! -- Licensed under the GPL v2 or later.
! --
! DROP FUNCTION instr(varchar,varchar,integer,integer);
! CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS '
  DECLARE
*************** DECLARE
*** 2445,2458 ****
      beg_index ALIAS FOR $3;
      pos integer NOT NULL DEFAULT 0;
!     ending integer;
      temp_str varchar;
      beg integer;
      length integer;
!     temp_int integer;
  BEGIN
      IF beg_index > 0 THEN
!         -- Get substring from 1 to beg_index
  
!         temp_str := substring(string FROM beg_index); 
!         pos := position(string_to_search IN temp_str);
  
--- 2494,2519 ----
      beg_index ALIAS FOR $3;
+     occur_index ALIAS FOR $4;
      pos integer NOT NULL DEFAULT 0;
!     occur_number integer NOT NULL DEFAULT 0;
      temp_str varchar;
      beg integer;
+     i integer;
      length integer;
!     ss_length integer;
  BEGIN
      IF beg_index > 0 THEN
!         beg := beg_index;
!         temp_str := substring(string FROM beg_index);
  
!         FOR i IN 1..occur_index LOOP
!             pos := position(string_to_search IN temp_str);
! 
!             IF i = 1 THEN
!                 beg := beg + pos - 1;
!             ELSE
!                 beg := beg + pos;
!             END IF;
! 
!             temp_str := substring(string FROM beg + 1);
!         END LOOP;
  
*************** BEGIN
*** 2461,2548 ****
          ELSE
!             RETURN pos + beg_index - 1;
          END IF;
- 
      ELSE
          length := char_length(string);
  
!         IF beg_index = -1 THEN
!             ending := length;
!             beg := ending;
!             temp_int := 1;
!         ELSE
!             ending := length - abs(beg_index);
!             beg := ending;
!             temp_int := ending - beg;
!         END IF;
! 
!         WHILE pos = 0 AND beg &lt;&gt; 1 LOOP
! 
!             temp_str := substring(string FROM beg FOR temp_int);
              pos := position(string_to_search IN temp_str);
  
!             -- Keep moving left
  
              beg := beg - 1;
-             temp_int := (ending - beg) + 1;
          END LOOP;
-     END IF;
  
-     IF pos = 0 THEN
          RETURN 0;
-     ELSE
-         RETURN beg + 1;
      END IF;
  END;
! ' LANGUAGE 'plpgsql';
! 
! --
! -- The next one (where all four params are passed) is in PL/Tcl
! -- because I had no more patience to do it in PL/pgSQL.
! -- It'd probably be faster in PL/pgSQL (that being the reason why
! -- I implemented the first two functions in PL/pgSQL) so someday I'll do it.
! --
! 
! DROP FUNCTION instr(varchar, varchar, integer, integer);
! CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS '
!     set string1 $1
!     set string2 $2
!     set n $3
!     set m $4
! 
!     if { $n > 0 } {
!         set pos [string first $string2 $string1 [expr $n -1]]
!         if { $pos < 0 } {
!             return 0
!         } else {
!             for { set i 1 } { $i < $m } { incr i } {
!                 set pos [string first $string2 $string1 [expr $pos + 1]]
!                 if { $pos < 0 } {
!                     return 0
!                 }
!             }
!         }
!     }
! 
!     if { $n < 0 } {
!         set pos [string last $string2 $string1 [expr [string length $string1] + $n]]
! 
!         if { $pos < 0 } {
!             return 0
!         } else {
!             for { set i 1 } { $i < $m } { incr i } {
!                 # n is negative so we add
!                 set pos [string last $string2 $string1 [expr $pos - 1]]
!                 if { $pos < 0 } {
!                     return 0
!                 }
!             }
!         }
!     }
! 
!     if { $pos < 0 } {
!         return 0
!     } else {
!         return [expr $pos + 1]
!     }
! ' LANGUAGE 'pltcl';
  </programlisting>
--- 2522,2549 ----
          ELSE
!             RETURN beg;
          END IF;
      ELSE
+         ss_length := char_length(string_to_search);
          length := char_length(string);
+         beg := length + beg_index - ss_length + 2;
  
!         WHILE beg > 0 LOOP
!             temp_str := substring(string FROM beg FOR ss_length);
              pos := position(string_to_search IN temp_str);
  
!             IF pos > 0 THEN
!                 occur_number := occur_number + 1;
! 
!                 IF occur_number = occur_index THEN
!                     RETURN beg;
!                 END IF;
!             END IF;
  
              beg := beg - 1;
          END LOOP;
  
          RETURN 0;
      END IF;
  END;
! ' language 'plpgsql';
  </programlisting>

--dDRMvlgZJXvWKvBx--