File: plpgsql-porting.html

package info (click to toggle)
pgadmin3 1.4.3-2
links: PTS
area: main
in suites: etch, etch-m68k
size: 29,796 kB
ctags: 10,758
sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (654 lines) | stat: -rw-r--r-- 27,143 bytes
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>36.11.Porting from Oracle PL/SQL</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plpgsql.html" title="Chapter36.PL/pgSQL - SQL Procedural Language">
<link rel="prev" href="plpgsql-trigger.html" title="36.10.Trigger Procedures">
<link rel="next" href="pltcl.html" title="Chapter37.PL/Tcl - Tcl Procedural Language">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plpgsql-porting"></a>36.11.Porting from <span class="productname">Oracle</span> PL/SQL</h2></div></div></div>
<a name="id727984"></a><a name="id727695"></a><p>   This section explains differences between
   <span class="productname">PostgreSQL</span>'s <span class="application">PL/pgSQL</span>
   language and Oracle's <span class="application">PL/SQL</span> language,
   to help developers who port applications from
   <span class="trademark">Oracle</span> to <span class="productname">PostgreSQL</span>.
  </p>
<p>   <span class="application">PL/pgSQL</span> is similar to PL/SQL in many
   aspects. It is a block-structured, imperative language, and all
   variables have to be declared.  Assignments, loops, conditionals
   are similar.  The main differences you should keep in mind when
   porting from <span class="application">PL/SQL</span> to
   <span class="application">PL/pgSQL</span> are:

    </p>
<div class="itemizedlist"><ul type="disc">
<li><p>       There are no default values for parameters in <span class="productname">PostgreSQL</span>.
      </p></li>
<li><p>       You cannot use parameter names that are the same as columns
       that are referenced in the function. Oracle allows you to do this
       if you qualify the parameter name using
       <code class="literal">function_name.paramater_name</code>.
      </p></li>
<li><p>       You can overload function names in <span class="productname">PostgreSQL</span>. This is
       often used to work around the lack of default parameters.
      </p></li>
<li><p>       No need for cursors in <span class="application">PL/pgSQL</span>, just put the
       query in the <code class="literal">FOR</code> statement.  (See <a href="plpgsql-porting.html#plpgsql-porting-ex2" title="Example36.6.Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example36.6, &#8220;Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>&#8221;</a>.)
      </p></li>
<li><p>       In <span class="productname">PostgreSQL</span> the function body must be written as
       a string literal.  Therefore you need to use dollar quoting or escape
       single quotes in the function body. See <a href="plpgsql-development-tips.html#plpgsql-quote-tips" title="36.2.1.Handling of Quotation Marks">Section36.2.1, &#8220;Handling of Quotation Marks&#8221;</a>.
      </p></li>
<li><p>       Instead of packages, use schemas to organize your functions
       into groups.
      </p></li>
<li><p>       Since there are no packages, there are no package-level variables
       either. This is somewhat annoying.  You can keep per-session state 
       in temporary tables instead.
      </p></li>
</ul></div>
<p>
   </p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id728100"></a>36.11.1.Porting Examples</h3></div></div></div>
<p>    <a href="plpgsql-porting.html#pgsql-porting-ex1" title="Example36.5.Porting a Simple Function from PL/SQL to PL/pgSQL">Example36.5, &#8220;Porting a Simple Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>&#8221;</a> shows how to port a simple
    function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>.
   </p>
<div class="example">
<a name="pgsql-porting-ex1"></a><p class="title"><b>Example36.5.Porting a Simple Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></b></p>
<div class="example-contents">
<p>     Here is an <span class="productname">Oracle</span> <span class="application">PL/SQL</span> function:
</p>
<pre class="programlisting">CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;</pre>
<p>
    </p>
<p>     Let's go through this function and see the differences compared to
     <span class="application">PL/pgSQL</span>:

     </p>
<div class="itemizedlist"><ul type="disc">
<li><p>        The <code class="literal">RETURN</code> key word in the function
        prototype (not the function body) becomes
        <code class="literal">RETURNS</code> in
        <span class="productname">PostgreSQL</span>.
        Also, <code class="literal">IS</code> becomes <code class="literal">AS</code>, and you need to
        add a <code class="literal">LANGUAGE</code> clause because <span class="application">PL/pgSQL</span>
        is not the only possible function language.
       </p></li>
<li><p>        In <span class="productname">PostgreSQL</span>, the function body is considered
        to be a string literal, so you need to use quote marks or dollar
        quotes around it.  This substitutes for the terminating <code class="literal">/</code>
        in the Oracle approach.
       </p></li>
<li><p>        The <code class="literal">show errors</code> command does not exist in
        <span class="productname">PostgreSQL</span>, and is not needed since errors are
        reported automatically.
       </p></li>
</ul></div>
<p>
    </p>
<p>     This is how this function would look when ported to
     <span class="productname">PostgreSQL</span>:

</p>
<pre class="programlisting">CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;</pre>
<p>
    </p>
</div>
</div>
<br class="example-break"><p>    <a href="plpgsql-porting.html#plpgsql-porting-ex2" title="Example36.6.Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example36.6, &#8220;Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>&#8221;</a> shows how to port a
    function that creates another function and how to handle the
    ensuing quoting problems.
   </p>
<div class="example">
<a name="plpgsql-porting-ex2"></a><p class="title"><b>Example36.6.Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></b></p>
<div class="example-contents">
<p>     The following procedure grabs rows from a
     <code class="command">SELECT</code> statement and builds a large function
     with the results in <code class="literal">IF</code> statements, for the
     sake of efficiency. Notice particularly the differences in the
     cursor and the <code class="literal">FOR</code> loop.
    </p>
<p>     This is the Oracle version:
</p>
<pre class="programlisting">CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS 
        SELECT * FROM cs_referrer_keys 
        ORDER BY try_order;

    func_cmd VARCHAR(4000); 
BEGIN 
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
                 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; 

    FOR referrer_key IN referrer_keys LOOP 
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;'; 
    END LOOP; 

    func_cmd := func_cmd || ' RETURN NULL; END;'; 

    EXECUTE IMMEDIATE func_cmd; 
END; 
/ 
show errors;</pre>
<p>
    </p>
<p>     Here is how this function would end up in <span class="productname">PostgreSQL</span>:
</p>
<pre class="programlisting">CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
    referrer_key RECORD;  -- declare a generic record to be used in a FOR
    func_body text;
    func_cmd text;
BEGIN 
    func_body := 'BEGIN';

    -- Notice how we scan through the results of a query in a FOR loop
    -- using the FOR &lt;record&gt; construct.

    FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP; 

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar) 
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;</pre>
<p>
     Notice how the body of the function is built separately and passed
     through <code class="literal">quote_literal</code> to double any quote marks in it.  This
     technique is needed because we cannot safely use dollar quoting for
     defining the new function: we do not know for sure what strings will
     be interpolated from the <code class="structfield">referrer_key.key_string</code> field.
     (We are assuming here that <code class="structfield">referrer_key.kind</code> can be
     trusted to always be <code class="literal">host</code>, <code class="literal">domain</code>, or
     <code class="literal">url</code>, but <code class="structfield">referrer_key.key_string</code> might be
     anything, in particular it might contain dollar signs.) This function
     is actually an improvement on the Oracle original, because it will
     not generate broken code when <code class="structfield">referrer_key.key_string</code> or
     <code class="structfield">referrer_key.referrer_type</code> contain quote marks.
    </p>
</div>
</div>
<br class="example-break"><p>    <a href="plpgsql-porting.html#plpgsql-porting-ex3" title="Example36.7.Porting a Procedure With String Manipulation and
    OUT Parameters from PL/SQL to
    PL/pgSQL">Example36.7, &#8220;Porting a Procedure With String Manipulation and
    <code class="literal">OUT</code> Parameters from <span class="application">PL/SQL</span> to
    <span class="application">PL/pgSQL</span>&#8221;</a> shows how to port a function
    with <code class="literal">OUT</code> parameters and string manipulation.
    <span class="productname">PostgreSQL</span> does not have a built-in
    <code class="function">instr</code> function, but you can create one
    using a combination of other
    functions.<a name="id728544"></a> In <a href="plpgsql-porting.html#plpgsql-porting-appendix" title="36.11.3.Appendix">Section36.11.3, &#8220;Appendix&#8221;</a> there is a
    <span class="application">PL/pgSQL</span> implementation of
    <code class="function">instr</code> that you can use to make your porting
    easier.
   </p>
<div class="example">
<a name="plpgsql-porting-ex3"></a><p class="title"><b>Example36.7.Porting a Procedure With String Manipulation and
    <code class="literal">OUT</code> Parameters from <span class="application">PL/SQL</span> to
    <span class="application">PL/pgSQL</span></b></p>
<div class="example-contents">
<p>     The following <span class="productname">Oracle</span> PL/SQL procedure is used
     to parse a URL and return several elements (host, path, and query).
    </p>
<p>     This is the Oracle version:
</p>
<pre class="programlisting">CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;</pre>
<p>
    </p>
<p>     Here is a possible translation into <span class="application">PL/pgSQL</span>:
</p>
<pre class="programlisting">CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;</pre>
<p>

     This function could be used like this:
</p>
<pre class="programlisting">SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');</pre>
<p>
    </p>
</div>
</div>
<br class="example-break"><p>    <a href="plpgsql-porting.html#plpgsql-porting-ex4" title="Example36.8.Porting a Procedure from PL/SQL to PL/pgSQL">Example36.8, &#8220;Porting a Procedure from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>&#8221;</a> shows how to port a procedure
    that uses numerous features that are specific to Oracle.
   </p>
<div class="example">
<a name="plpgsql-porting-ex4"></a><p class="title"><b>Example36.8.Porting a Procedure from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></b></p>
<div class="example-contents">
<p>     The Oracle version:

</p>
<pre class="programlisting">CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION;<a name="co.plpgsql-porting-pragma"></a>(1)
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<a name="co.plpgsql-porting-locktable"></a>(2)

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count &gt; 0 THEN
        COMMIT; -- free lock<a name="co.plpgsql-porting-commit"></a>(3)
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
    END;
    COMMIT;
END;
/
show errors</pre>
<p>
   </p>
<p>    Procedures like this can easily be converted into <span class="productname">PostgreSQL</span>
    functions returning <code class="type">void</code>. This procedure in
    particular is interesting because it can teach us some things:

    </p>
<div class="calloutlist"><table border="0" summary="Callout list">
<tr>
<td width="5%" valign="top" align="left">
<a href="#co.plpgsql-porting-pragma">(1)</a> </td>
<td valign="top" align="left"><p>       There is no <code class="literal">PRAGMA</code> statement in <span class="productname">PostgreSQL</span>.
      </p></td>
</tr>
<tr>
<td width="5%" valign="top" align="left">
<a href="#co.plpgsql-porting-locktable">(2)</a> </td>
<td valign="top" align="left"><p>       If you do a <code class="command">LOCK TABLE</code> in <span class="application">PL/pgSQL</span>,
       the lock will not be released until the calling transaction is
       finished.
      </p></td>
</tr>
<tr>
<td width="5%" valign="top" align="left">
<a href="#co.plpgsql-porting-commit">(3)</a> </td>
<td valign="top" align="left"><p>       You cannot issue <code class="command">COMMIT</code> in a
       <span class="application">PL/pgSQL</span> function.  The function is
       running within some outer transaction and so <code class="command">COMMIT</code>
       would imply terminating the function's execution.  However, in
       this particular case it is not necessary anyway, because the lock
       obtained by the <code class="command">LOCK TABLE</code> will be released when
       we raise an error.
      </p></td>
</tr>
</table></div>
<p>
   </p>
<p>    This is how we could port this procedure to <span class="application">PL/pgSQL</span>:

</p>
<pre class="programlisting">CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count &gt; 0 THEN
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<a name="co.plpgsql-porting-raise"></a>(1)
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN <a name="co.plpgsql-porting-exception"></a>(2)
            -- don't worry if it already exists
    END;
END;
$$ LANGUAGE plpgsql;</pre>
<p>

    </p>
<div class="calloutlist"><table border="0" summary="Callout list">
<tr>
<td width="5%" valign="top" align="left">
<a href="#co.plpgsql-porting-raise">(1)</a> </td>
<td valign="top" align="left"><p>       The syntax of <code class="literal">RAISE</code> is considerably different from
       Oracle's similar statement.
      </p></td>
</tr>
<tr>
<td width="5%" valign="top" align="left">
<a href="#co.plpgsql-porting-exception">(2)</a> </td>
<td valign="top" align="left"><p>       The exception names supported by <span class="application">PL/pgSQL</span> are
       different from Oracle's.  The set of built-in exception names
       is much larger (see <a href="errcodes-appendix.html" title="AppendixA.PostgreSQL Error Codes">AppendixA, <i><span class="productname">PostgreSQL</span> Error Codes</i></a>).  There
       is not currently a way to declare user-defined exception names.
      </p></td>
</tr>
</table></div>
<p>

    The main functional difference between this procedure and the
    Oracle equivalent is that the exclusive lock on the <code class="literal">cs_jobs</code>
    table will be held until the calling transaction completes.  Also, if
    the caller later aborts (for example due to an error), the effects of
    this procedure will be rolled back.
   </p>
</div>
</div>
<br class="example-break">
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-porting-other"></a>36.11.2.Other Things to Watch For</h3></div></div></div>
<p>    This section explains a few other things to watch for when porting
    Oracle <span class="application">PL/SQL</span> functions to
    <span class="productname">PostgreSQL</span>.
   </p>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="plpgsql-porting-exceptions"></a>36.11.2.1.Implicit Rollback after Exceptions</h4></div></div></div>
<p>     In <span class="application">PL/pgSQL</span>, when an exception is caught by an
     <code class="literal">EXCEPTION</code> clause, all database changes since the block's
     <code class="literal">BEGIN</code> are automatically rolled back.  That is, the behavior
     is equivalent to what you'd get in Oracle with

</p>
<pre class="programlisting">    BEGIN
        SAVEPOINT s1;
        ... code here ...
    EXCEPTION
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
    END;</pre>
<p>

     If you are translating an Oracle procedure that uses
     <code class="command">SAVEPOINT</code> and <code class="command">ROLLBACK TO</code> in this style,
     your task is easy: just omit the <code class="command">SAVEPOINT</code> and
     <code class="command">ROLLBACK TO</code>.  If you have a procedure that uses
     <code class="command">SAVEPOINT</code> and <code class="command">ROLLBACK TO</code> in a different way
     then some actual thought will be required.
    </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="id729090"></a>36.11.2.2.<code class="command">EXECUTE</code></h4></div></div></div>
<p>     The <span class="application">PL/pgSQL</span> version of
     <code class="command">EXECUTE</code> works similarly to the
     <span class="application">PL/SQL</span> version, but you have to remember to use
     <code class="function">quote_literal</code> and
     <code class="function">quote_ident</code> as described in <a href="plpgsql-statements.html#plpgsql-statements-executing-dyn" title="36.6.5.Executing Dynamic Commands">Section36.6.5, &#8220;Executing Dynamic Commands&#8221;</a>.  Constructs of the
     type <code class="literal">EXECUTE 'SELECT * FROM $1';</code> will not work
     unless you use these functions.
    </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="plpgsql-porting-optimization"></a>36.11.2.3.Optimizing <span class="application">PL/pgSQL</span> Functions</h4></div></div></div>
<p>     <span class="productname">PostgreSQL</span> gives you two function creation
     modifiers to optimize execution: &#8220;<span class="quote">volatility</span>&#8221; (whether the
     function always returns the same result when given the same
     arguments) and &#8220;<span class="quote">strictness</span>&#8221; (whether the
     function returns null if any argument is null).  Consult the
     <a href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle"><a name="sql-createfunction-title"></a>CREATE FUNCTION</span></a> reference page for details.
    </p>
<p>     When making use of these optimization attributes, your
     <code class="command">CREATE FUNCTION</code> statement might look something
     like this:

</p>
<pre class="programlisting">CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;</pre>
<p>
    </p>
</div>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-porting-appendix"></a>36.11.3.Appendix</h3></div></div></div>
<p>    This section contains the code for a set of Oracle-compatible
    <code class="function">instr</code> functions that you can use to simplify
    your porting efforts.
   </p>
<pre class="programlisting">--
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
-- 
-- Searches string1 beginning at the nth character for the mth occurrence
-- of string2.  If n is negative, search backwards.  If m is not passed,
-- assume 1 (search starts at first character).
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
DECLARE
    pos integer;
BEGIN
    pos:= instr($1, $2, 1);
    RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index &gt; 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 &gt; 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos &gt; 0 THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    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 &gt; 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;

        IF pos = 0 THEN
            RETURN 0;
        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 &gt; 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos &gt; 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 STRICT IMMUTABLE;</pre>
</div>
</div></body>
</html>