~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : contents of doc/src/sgml/html/plpgsql-porting.html at revision 27

~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-updates : (revision 27)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Porting from Oracle PL/SQL</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.13 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/pgSQL - SQL Procedural Language"
HREF="plpgsql.html"><LINK
REL="PREVIOUS"
TITLE="Tips for Developing in PL/pgSQL"
HREF="plpgsql-development-tips.html"><LINK
REL="NEXT"
TITLE="PL/Tcl - Tcl Procedural Language"
HREF="pltcl.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2016-05-09T21:13:26"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.13 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Tips for Developing in PL/pgSQL"
HREF="plpgsql-development-tips.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 40. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="PL/Tcl - Tcl Procedural Language"
HREF="pltcl.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-PORTING"
>40.12. Porting from <SPAN
CLASS="PRODUCTNAME"
>Oracle</SPAN
> PL/SQL</A
></H1
><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
>&reg; 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
></P
></P><UL
><LI
><P
>       If a name used in a SQL command could be either a column name of a
       table or a reference to a variable of the function,
       <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> treats it as a column name.  This corresponds
       to <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>'s
       <TT
CLASS="LITERAL"
>plpgsql.variable_conflict</TT
> = <TT
CLASS="LITERAL"
>use_column</TT
>
       behavior, which is not the default,
       as explained in <A
HREF="plpgsql-implementation.html#PLPGSQL-VAR-SUBST"
>Section 40.10.1</A
>.
       It's often best to avoid such ambiguities in the first place,
       but if you have to port a large amount of code that depends on
       this behavior, setting <TT
CLASS="LITERAL"
>variable_conflict</TT
> may be the
       best solution.
      </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"
>Section 40.11.1</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
><LI
><P
>       Integer <TT
CLASS="COMMAND"
>FOR</TT
> loops with <TT
CLASS="LITERAL"
>REVERSE</TT
> work
       differently: <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> counts down from the second
       number to the first, while <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> counts down
       from the first number to the second, requiring the loop bounds
       to be swapped when porting.  This incompatibility is unfortunate
       but is unlikely to be changed. (See <A
HREF="plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR"
>Section 40.6.3.5</A
>.)
      </P
></LI
><LI
><P
>       <TT
CLASS="COMMAND"
>FOR</TT
> loops over queries (other than cursors) also work
       differently: the target variable(s) must have been declared,
       whereas <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> always declares them implicitly.
       An advantage of this is that the variable values are still accessible
       after the loop exits.
      </P
></LI
><LI
><P
>       There are various notational differences for the use of cursor
       variables.
      </P
></LI
></UL
><P>
   </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN59912"
>40.12.1. Porting Examples</A
></H2
><P
>    <A
HREF="plpgsql-porting.html#PGSQL-PORTING-EX1"
>Example 40-8</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
><B
>Example 40-8. Porting a Simple Function from <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> to <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></B
></P
><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
></P
></P><UL
><LI
><P
>        The <TT
CLASS="LITERAL"
>RETURN</TT
> key word in the function
        prototype (not the function body) becomes
        <TT
CLASS="LITERAL"
>RETURNS</TT
> in
        <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
        Also, <TT
CLASS="LITERAL"
>IS</TT
> becomes <TT
CLASS="LITERAL"
>AS</TT
>, and you need to
        add a <TT
CLASS="LITERAL"
>LANGUAGE</TT
> 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 <TT
CLASS="LITERAL"
>/</TT
>
        in the Oracle approach.
       </P
></LI
><LI
><P
>        The <TT
CLASS="LITERAL"
>show errors</TT
> command does not exist in
        <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>, and is not needed since errors are
        reported automatically.
       </P
></LI
></UL
><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
><P
>    <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX2"
>Example 40-9</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
><B
>Example 40-9. Porting a Function that Creates Another Function from <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> to <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></B
></P
><P
>     The following procedure grabs rows from a
     <TT
CLASS="COMMAND"
>SELECT</TT
> statement and builds a large function
     with the results in <TT
CLASS="LITERAL"
>IF</TT
> statements, for the
     sake of efficiency.
    </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_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEGIN
    func_body := 'BEGIN';

    FOR referrer_key IN referrer_keys 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 <TT
CLASS="LITERAL"
>quote_literal</TT
> 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 <TT
CLASS="STRUCTFIELD"
>referrer_key.key_string</TT
> field.
     (We are assuming here that <TT
CLASS="STRUCTFIELD"
>referrer_key.kind</TT
> can be
     trusted to always be <TT
CLASS="LITERAL"
>host</TT
>, <TT
CLASS="LITERAL"
>domain</TT
>, or
     <TT
CLASS="LITERAL"
>url</TT
>, but <TT
CLASS="STRUCTFIELD"
>referrer_key.key_string</TT
> 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 <TT
CLASS="STRUCTFIELD"
>referrer_key.key_string</TT
> or
     <TT
CLASS="STRUCTFIELD"
>referrer_key.referrer_type</TT
> contain quote marks.
    </P
></DIV
><P
>    <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX3"
>Example 40-10</A
> shows how to port a function
    with <TT
CLASS="LITERAL"
>OUT</TT
> 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. In <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX"
>Section 40.12.3</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
><B
>Example 40-10. Porting a Procedure With String Manipulation and
    <TT
CLASS="LITERAL"
>OUT</TT
> Parameters from <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> to
    <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></B
></P
><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
><P
>    <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX4"
>Example 40-11</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
><B
>Example 40-11. Porting a Procedure from <SPAN
CLASS="APPLICATION"
>PL/SQL</SPAN
> to <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></B
></P
><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"
><B
>(1)</B
></A
>
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<A
NAME="CO.PLPGSQL-PORTING-LOCKTABLE"
><B
>(2)</B
></A
>

    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"
><B
>(3)</B
></A
>
        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 <TT
CLASS="TYPE"
>void</TT
>. This procedure in
    particular is interesting because it can teach us some things:

    <DIV
CLASS="CALLOUTLIST"
><DL
COMPACT="COMPACT"
><DT
><A
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-PRAGMA"
><B
>(1)</B
></A
></DT
><DD
>       There is no <TT
CLASS="LITERAL"
>PRAGMA</TT
> statement in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
      </DD
><DT
><A
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-LOCKTABLE"
><B
>(2)</B
></A
></DT
><DD
>       If you do a <TT
CLASS="COMMAND"
>LOCK TABLE</TT
> in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>,
       the lock will not be released until the calling transaction is
       finished.
      </DD
><DT
><A
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-COMMIT"
><B
>(3)</B
></A
></DT
><DD
>       You cannot issue <TT
CLASS="COMMAND"
>COMMIT</TT
> in a
       <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function.  The function is
       running within some outer transaction and so <TT
CLASS="COMMAND"
>COMMIT</TT
>
       would imply terminating the function's execution.  However, in
       this particular case it is not necessary anyway, because the lock
       obtained by the <TT
CLASS="COMMAND"
>LOCK TABLE</TT
> will be released when
       we raise an error.
      </DD
></DL
></DIV
>
   </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"
><B
>(1)</B
></A
>
    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"
><B
>(2)</B
></A
>
            -- don't worry if it already exists
    END;
END;
$$ LANGUAGE plpgsql;</PRE
><P>

    <DIV
CLASS="CALLOUTLIST"
><DL
COMPACT="COMPACT"
><DT
><A
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-RAISE"
><B
>(1)</B
></A
></DT
><DD
>       The syntax of <TT
CLASS="LITERAL"
>RAISE</TT
> is considerably different from
       Oracle's statement, although the basic case <TT
CLASS="LITERAL"
>RAISE</TT
>
       <TT
CLASS="REPLACEABLE"
><I
>exception_name</I
></TT
> works
       similarly.
      </DD
><DT
><A
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-EXCEPTION"
><B
>(2)</B
></A
></DT
><DD
>       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"
>Appendix A</A
>).  There
       is not currently a way to declare user-defined exception names,
       although you can throw user-chosen SQLSTATE values instead.
      </DD
></DL
></DIV
>

    The main functional difference between this procedure and the
    Oracle equivalent is that the exclusive lock on the <TT
CLASS="LITERAL"
>cs_jobs</TT
>
    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
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-PORTING-OTHER"
>40.12.2. Other Things to Watch For</A
></H2
><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"
><H3
CLASS="SECT3"
><A
NAME="PLPGSQL-PORTING-EXCEPTIONS"
>40.12.2.1. Implicit Rollback after Exceptions</A
></H3
><P
>     In <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>, when an exception is caught by an
     <TT
CLASS="LITERAL"
>EXCEPTION</TT
> clause, all database changes since the block's
     <TT
CLASS="LITERAL"
>BEGIN</TT
> 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
     <TT
CLASS="COMMAND"
>SAVEPOINT</TT
> and <TT
CLASS="COMMAND"
>ROLLBACK TO</TT
> in this style,
     your task is easy: just omit the <TT
CLASS="COMMAND"
>SAVEPOINT</TT
> and
     <TT
CLASS="COMMAND"
>ROLLBACK TO</TT
>.  If you have a procedure that uses
     <TT
CLASS="COMMAND"
>SAVEPOINT</TT
> and <TT
CLASS="COMMAND"
>ROLLBACK TO</TT
> in a different way
     then some actual thought will be required.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN60058"
>40.12.2.2. <TT
CLASS="COMMAND"
>EXECUTE</TT
></A
></H3
><P
>     The <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> version of
     <TT
CLASS="COMMAND"
>EXECUTE</TT
> 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"
>Section 40.5.4</A
>.  Constructs of the
     type <TT
CLASS="LITERAL"
>EXECUTE 'SELECT * FROM $1';</TT
> will not work
     reliably unless you use these functions.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="PLPGSQL-PORTING-OPTIMIZATION"
>40.12.2.3. Optimizing <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Functions</A
></H3
><P
>     <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> gives you two function creation
     modifiers to optimize execution: <SPAN
CLASS="QUOTE"
>"volatility"</SPAN
> (whether
     the function always returns the same result when given the same
     arguments) and <SPAN
CLASS="QUOTE"
>"strictness"</SPAN
> (whether the function
     returns null if any argument is null).  Consult the <A
HREF="sql-createfunction.html"
>CREATE FUNCTION</A
>
     reference page for details.
    </P
><P
>     When making use of these optimization attributes, your
     <TT
CLASS="COMMAND"
>CREATE FUNCTION</TT
> 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"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-PORTING-APPENDIX"
>40.12.3. Appendix</A
></H2
><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;
    ELSIF beg_index &lt; 0 THEN
        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;
    ELSE
        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;
    ELSIF beg_index &lt; 0 THEN
        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;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;</PRE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-development-tips.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="pltcl.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Tips for Developing in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>PL/Tcl - Tcl Procedural Language</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>