begriffs open source - ai-pg/blob - full-docs/src/sgml/html/plpgsql-porting.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>41.13. Porting from Oracle PL/SQL</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-development-tips.html" title="41.12. Tips for Developing in PL/pgSQL" /><link rel="next" href="pltcl.html" title="Chapter 42. PL/Tcl — Tcl Procedural Language" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">41.13. Porting from <span class="productname">Oracle</span> PL/SQL</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-development-tips.html" title="41.12. Tips for Developing in PL/pgSQL">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 41. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pltcl.html" title="Chapter 42. PL/Tcl — Tcl Procedural Language">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-PORTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.13. Porting from <span class="productname">Oracle</span> PL/SQL <a href="#PLPGSQL-PORTING" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-porting.html#PLPGSQL-PORTING-EXAMPLES">41.13.1. Porting Examples</a></span></dt><dt><span class="sect2"><a href="plpgsql-porting.html#PLPGSQL-PORTING-OTHER">41.13.2. Other Things to Watch For</a></span></dt><dt><span class="sect2"><a href="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX">41.13.3. Appendix</a></span></dt></dl></div><a id="id-1.8.8.15.2" class="indexterm"></a><a id="id-1.8.8.15.3" class="indexterm"></a><p>
   3    This section explains differences between
   4    <span class="productname">PostgreSQL</span>'s <span class="application">PL/pgSQL</span>
   5    language and Oracle's <span class="application">PL/SQL</span> language,
   6    to help developers who port applications from
   7    <span class="trademark">Oracle</span>® to <span class="productname">PostgreSQL</span>.
   8   </p><p>
   9    <span class="application">PL/pgSQL</span> is similar to PL/SQL in many
  10    aspects. It is a block-structured, imperative language, and all
  11    variables have to be declared.  Assignments, loops, and conditionals
  12    are similar.  The main differences you should keep in mind when
  13    porting from <span class="application">PL/SQL</span> to
  14    <span class="application">PL/pgSQL</span> are:
  15
  16     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  17        If a name used in an SQL command could be either a column name of a
  18        table used in the command or a reference to a variable of the function,
  19        <span class="application">PL/SQL</span> treats it as a column name.
  20        By default, <span class="application">PL/pgSQL</span> will throw an error
  21        complaining that the name is ambiguous.  You can specify
  22        <code class="literal">plpgsql.variable_conflict</code> = <code class="literal">use_column</code>
  23        to change this behavior to match <span class="application">PL/SQL</span>,
  24        as explained in <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="41.11.1. Variable Substitution">Section 41.11.1</a>.
  25        It's often best to avoid such ambiguities in the first place,
  26        but if you have to port a large amount of code that depends on
  27        this behavior, setting <code class="literal">variable_conflict</code> may be the
  28        best solution.
  29       </p></li><li class="listitem"><p>
  30        In <span class="productname">PostgreSQL</span> the function body must be written as
  31        a string literal.  Therefore you need to use dollar quoting or escape
  32        single quotes in the function body. (See <a class="xref" href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS" title="41.12.1. Handling of Quotation Marks">Section 41.12.1</a>.)
  33       </p></li><li class="listitem"><p>
  34        Data type names often need translation.  For example, in Oracle string
  35        values are commonly declared as being of type <code class="type">varchar2</code>, which
  36        is a non-SQL-standard type.  In <span class="productname">PostgreSQL</span>,
  37        use type <code class="type">varchar</code> or <code class="type">text</code> instead.  Similarly, replace
  38        type <code class="type">number</code> with <code class="type">numeric</code>, or use some other numeric
  39        data type if there's a more appropriate one.
  40       </p></li><li class="listitem"><p>
  41        Instead of packages, use schemas to organize your functions
  42        into groups.
  43       </p></li><li class="listitem"><p>
  44        Since there are no packages, there are no package-level variables
  45        either. This is somewhat annoying.  You can keep per-session state
  46        in temporary tables instead.
  47       </p></li><li class="listitem"><p>
  48        Integer <code class="command">FOR</code> loops with <code class="literal">REVERSE</code> work
  49        differently: <span class="application">PL/SQL</span> counts down from the second
  50        number to the first, while <span class="application">PL/pgSQL</span> counts down
  51        from the first number to the second, requiring the loop bounds
  52        to be swapped when porting.  This incompatibility is unfortunate
  53        but is unlikely to be changed. (See <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR" title="41.6.5.5. FOR (Integer Variant)">Section 41.6.5.5</a>.)
  54       </p></li><li class="listitem"><p>
  55        <code class="command">FOR</code> loops over queries (other than cursors) also work
  56        differently: the target variable(s) must have been declared,
  57        whereas <span class="application">PL/SQL</span> always declares them implicitly.
  58        An advantage of this is that the variable values are still accessible
  59        after the loop exits.
  60       </p></li><li class="listitem"><p>
  61        There are various notational differences for the use of cursor
  62        variables.
  63       </p></li></ul></div><p>
  64    </p><div class="sect2" id="PLPGSQL-PORTING-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">41.13.1. Porting Examples <a href="#PLPGSQL-PORTING-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>
  65     <a class="xref" href="plpgsql-porting.html#PGSQL-PORTING-EX1" title="Example 41.9. Porting a Simple Function from PL/SQL to PL/pgSQL">Example 41.9</a> shows how to port a simple
  66     function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>.
  67    </p><div class="example" id="PGSQL-PORTING-EX1"><p class="title"><strong>Example 41.9. Porting a Simple Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></strong></p><div class="example-contents"><p>
  68      Here is an <span class="productname">Oracle</span> <span class="application">PL/SQL</span> function:
  69 </p><pre class="programlisting">
  70 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
  71                                                   v_version varchar2)
  72 RETURN varchar2 IS
  73 BEGIN
  74     IF v_version IS NULL THEN
  75         RETURN v_name;
  76     END IF;
  77     RETURN v_name || '/' || v_version;
  78 END;
  79 /
  80 show errors;
  81 </pre><p>
  82     </p><p>
  83      Let's go through this function and see the differences compared to
  84      <span class="application">PL/pgSQL</span>:
  85
  86      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  87         The type name <code class="type">varchar2</code> has to be changed to <code class="type">varchar</code>
  88         or <code class="type">text</code>.  In the examples in this section, we'll
  89         use <code class="type">varchar</code>, but <code class="type">text</code> is often a better choice if
  90         you do not need specific string length limits.
  91        </p></li><li class="listitem"><p>
  92         The <code class="literal">RETURN</code> key word in the function
  93         prototype (not the function body) becomes
  94         <code class="literal">RETURNS</code> in
  95         <span class="productname">PostgreSQL</span>.
  96         Also, <code class="literal">IS</code> becomes <code class="literal">AS</code>, and you need to
  97         add a <code class="literal">LANGUAGE</code> clause because <span class="application">PL/pgSQL</span>
  98         is not the only possible function language.
  99        </p></li><li class="listitem"><p>
 100         In <span class="productname">PostgreSQL</span>, the function body is considered
 101         to be a string literal, so you need to use quote marks or dollar
 102         quotes around it.  This substitutes for the terminating <code class="literal">/</code>
 103         in the Oracle approach.
 104        </p></li><li class="listitem"><p>
 105         The <code class="literal">show errors</code> command does not exist in
 106         <span class="productname">PostgreSQL</span>, and is not needed since errors are
 107         reported automatically.
 108        </p></li></ul></div><p>
 109     </p><p>
 110      This is how this function would look when ported to
 111      <span class="productname">PostgreSQL</span>:
 112
 113 </p><pre class="programlisting">
 114 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
 115                                                   v_version varchar)
 116 RETURNS varchar AS $$
 117 BEGIN
 118     IF v_version IS NULL THEN
 119         RETURN v_name;
 120     END IF;
 121     RETURN v_name || '/' || v_version;
 122 END;
 123 $$ LANGUAGE plpgsql;
 124 </pre><p>
 125     </p></div></div><br class="example-break" /><p>
 126     <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 41.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 41.10</a> shows how to port a
 127     function that creates another function and how to handle the
 128     ensuing quoting problems.
 129    </p><div class="example" id="PLPGSQL-PORTING-EX2"><p class="title"><strong>Example 41.10. Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></strong></p><div class="example-contents"><p>
 130      The following procedure grabs rows from a
 131      <code class="command">SELECT</code> statement and builds a large function
 132      with the results in <code class="literal">IF</code> statements, for the
 133      sake of efficiency.
 134     </p><p>
 135      This is the Oracle version:
 136 </p><pre class="programlisting">
 137 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
 138     CURSOR referrer_keys IS
 139         SELECT * FROM cs_referrer_keys
 140         ORDER BY try_order;
 141     func_cmd VARCHAR(4000);
 142 BEGIN
 143     func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
 144                  v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
 145
 146     FOR referrer_key IN referrer_keys LOOP
 147         func_cmd := func_cmd ||
 148           ' IF v_' || referrer_key.kind
 149           || ' LIKE ''' || referrer_key.key_string
 150           || ''' THEN RETURN ''' || referrer_key.referrer_type
 151           || '''; END IF;';
 152     END LOOP;
 153
 154     func_cmd := func_cmd || ' RETURN NULL; END;';
 155
 156     EXECUTE IMMEDIATE func_cmd;
 157 END;
 158 /
 159 show errors;
 160 </pre><p>
 161     </p><p>
 162      Here is how this function would end up in <span class="productname">PostgreSQL</span>:
 163 </p><pre class="programlisting">
 164 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
 165 DECLARE
 166     referrer_keys CURSOR IS
 167         SELECT * FROM cs_referrer_keys
 168         ORDER BY try_order;
 169     func_body text;
 170     func_cmd text;
 171 BEGIN
 172     func_body := 'BEGIN';
 173
 174     FOR referrer_key IN referrer_keys LOOP
 175         func_body := func_body ||
 176           ' IF v_' || referrer_key.kind
 177           || ' LIKE ' || quote_literal(referrer_key.key_string)
 178           || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
 179           || '; END IF;' ;
 180     END LOOP;
 181
 182     func_body := func_body || ' RETURN NULL; END;';
 183
 184     func_cmd :=
 185       'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
 186                                                         v_domain varchar,
 187                                                         v_url varchar)
 188         RETURNS varchar AS '
 189       || quote_literal(func_body)
 190       || ' LANGUAGE plpgsql;' ;
 191
 192     EXECUTE func_cmd;
 193 END;
 194 $func$ LANGUAGE plpgsql;
 195 </pre><p>
 196      Notice how the body of the function is built separately and passed
 197      through <code class="literal">quote_literal</code> to double any quote marks in it.  This
 198      technique is needed because we cannot safely use dollar quoting for
 199      defining the new function: we do not know for sure what strings will
 200      be interpolated from the <code class="structfield">referrer_key.key_string</code> field.
 201      (We are assuming here that <code class="structfield">referrer_key.kind</code> can be
 202      trusted to always be <code class="literal">host</code>, <code class="literal">domain</code>, or
 203      <code class="literal">url</code>, but <code class="structfield">referrer_key.key_string</code> might be
 204      anything, in particular it might contain dollar signs.) This function
 205      is actually an improvement on the Oracle original, because it will
 206      not generate broken code when <code class="structfield">referrer_key.key_string</code> or
 207      <code class="structfield">referrer_key.referrer_type</code> contain quote marks.
 208     </p></div></div><br class="example-break" /><p>
 209     <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX3" title="Example 41.11. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to PL/pgSQL">Example 41.11</a> shows how to port a function
 210     with <code class="literal">OUT</code> parameters and string manipulation.
 211     <span class="productname">PostgreSQL</span> does not have a built-in
 212     <code class="function">instr</code> function, but you can create one
 213     using a combination of other
 214     functions. In <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX" title="41.13.3. Appendix">Section 41.13.3</a> there is a
 215     <span class="application">PL/pgSQL</span> implementation of
 216     <code class="function">instr</code> that you can use to make your porting
 217     easier.
 218    </p><div class="example" id="PLPGSQL-PORTING-EX3"><p class="title"><strong>Example 41.11. Porting a Procedure With String Manipulation and
 219     <code class="literal">OUT</code> Parameters from <span class="application">PL/SQL</span> to
 220     <span class="application">PL/pgSQL</span></strong></p><div class="example-contents"><p>
 221      The following <span class="productname">Oracle</span> PL/SQL procedure is used
 222      to parse a URL and return several elements (host, path, and query).
 223     </p><p>
 224      This is the Oracle version:
 225 </p><pre class="programlisting">
 226 CREATE OR REPLACE PROCEDURE cs_parse_url(
 227     v_url IN VARCHAR2,
 228     v_host OUT VARCHAR2,  -- This will be passed back
 229     v_path OUT VARCHAR2,  -- This one too
 230     v_query OUT VARCHAR2) -- And this one
 231 IS
 232     a_pos1 INTEGER;
 233     a_pos2 INTEGER;
 234 BEGIN
 235     v_host := NULL;
 236     v_path := NULL;
 237     v_query := NULL;
 238     a_pos1 := instr(v_url, '//');
 239
 240     IF a_pos1 = 0 THEN
 241         RETURN;
 242     END IF;
 243     a_pos2 := instr(v_url, '/', a_pos1 + 2);
 244     IF a_pos2 = 0 THEN
 245         v_host := substr(v_url, a_pos1 + 2);
 246         v_path := '/';
 247         RETURN;
 248     END IF;
 249
 250     v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
 251     a_pos1 := instr(v_url, '?', a_pos2 + 1);
 252
 253     IF a_pos1 = 0 THEN
 254         v_path := substr(v_url, a_pos2);
 255         RETURN;
 256     END IF;
 257
 258     v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
 259     v_query := substr(v_url, a_pos1 + 1);
 260 END;
 261 /
 262 show errors;
 263 </pre><p>
 264     </p><p>
 265      Here is a possible translation into <span class="application">PL/pgSQL</span>:
 266 </p><pre class="programlisting">
 267 CREATE OR REPLACE FUNCTION cs_parse_url(
 268     v_url IN VARCHAR,
 269     v_host OUT VARCHAR,  -- This will be passed back
 270     v_path OUT VARCHAR,  -- This one too
 271     v_query OUT VARCHAR) -- And this one
 272 AS $$
 273 DECLARE
 274     a_pos1 INTEGER;
 275     a_pos2 INTEGER;
 276 BEGIN
 277     v_host := NULL;
 278     v_path := NULL;
 279     v_query := NULL;
 280     a_pos1 := instr(v_url, '//');
 281
 282     IF a_pos1 = 0 THEN
 283         RETURN;
 284     END IF;
 285     a_pos2 := instr(v_url, '/', a_pos1 + 2);
 286     IF a_pos2 = 0 THEN
 287         v_host := substr(v_url, a_pos1 + 2);
 288         v_path := '/';
 289         RETURN;
 290     END IF;
 291
 292     v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
 293     a_pos1 := instr(v_url, '?', a_pos2 + 1);
 294
 295     IF a_pos1 = 0 THEN
 296         v_path := substr(v_url, a_pos2);
 297         RETURN;
 298     END IF;
 299
 300     v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
 301     v_query := substr(v_url, a_pos1 + 1);
 302 END;
 303 $$ LANGUAGE plpgsql;
 304 </pre><p>
 305
 306      This function could be used like this:
 307 </p><pre class="programlisting">
 308 SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
 309 </pre><p>
 310     </p></div></div><br class="example-break" /><p>
 311     <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX4" title="Example 41.12. Porting a Procedure from PL/SQL to PL/pgSQL">Example 41.12</a> shows how to port a procedure
 312     that uses numerous features that are specific to Oracle.
 313    </p><div class="example" id="PLPGSQL-PORTING-EX4"><p class="title"><strong>Example 41.12. Porting a Procedure from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span></strong></p><div class="example-contents"><p>
 314      The Oracle version:
 315
 316 </p><pre class="programlisting">
 317 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
 318     a_running_job_count INTEGER;
 319 BEGIN
 320     LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
 321
 322     SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
 323
 324     IF a_running_job_count &gt; 0 THEN
 325         COMMIT; -- free lock
 326         raise_application_error(-20000,
 327                  'Unable to create a new job: a job is currently running.');
 328     END IF;
 329
 330     DELETE FROM cs_active_job;
 331     INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
 332
 333     BEGIN
 334         INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
 335     EXCEPTION
 336         WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
 337     END;
 338     COMMIT;
 339 END;
 340 /
 341 show errors
 342 </pre><p>
 343    </p><p>
 344     This is how we could port this procedure to <span class="application">PL/pgSQL</span>:
 345
 346 </p><pre class="programlisting">
 347 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
 348 DECLARE
 349     a_running_job_count integer;
 350 BEGIN
 351     LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
 352
 353     SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
 354
 355     IF a_running_job_count &gt; 0 THEN
 356         COMMIT; -- free lock
 357         RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <span id="co.plpgsql-porting-raise"></span>(1)
 358     END IF;
 359
 360     DELETE FROM cs_active_job;
 361     INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
 362
 363     BEGIN
 364         INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
 365     EXCEPTION
 366         WHEN unique_violation THEN -- <span id="co.plpgsql-porting-exception"></span>(2)
 367             -- don't worry if it already exists
 368     END;
 369     COMMIT;
 370 END;
 371 $$ LANGUAGE plpgsql;
 372 </pre><p>
 373
 374     </p><div class="calloutlist"><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#co.plpgsql-porting-raise">(1)</a> </p></td><td valign="top" align="left"><p>
 375        The syntax of <code class="literal">RAISE</code> is considerably different from
 376        Oracle's statement, although the basic case <code class="literal">RAISE</code>
 377        <em class="replaceable"><code>exception_name</code></em> works
 378        similarly.
 379       </p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#co.plpgsql-porting-exception">(2)</a> </p></td><td valign="top" align="left"><p>
 380        The exception names supported by <span class="application">PL/pgSQL</span> are
 381        different from Oracle's.  The set of built-in exception names
 382        is much larger (see <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>).  There
 383        is not currently a way to declare user-defined exception names,
 384        although you can throw user-chosen SQLSTATE values instead.
 385       </p></td></tr></table></div><p>
 386    </p></div></div><br class="example-break" /></div><div class="sect2" id="PLPGSQL-PORTING-OTHER"><div class="titlepage"><div><div><h3 class="title">41.13.2. Other Things to Watch For <a href="#PLPGSQL-PORTING-OTHER" class="id_link">#</a></h3></div></div></div><p>
 387     This section explains a few other things to watch for when porting
 388     Oracle <span class="application">PL/SQL</span> functions to
 389     <span class="productname">PostgreSQL</span>.
 390    </p><div class="sect3" id="PLPGSQL-PORTING-EXCEPTIONS"><div class="titlepage"><div><div><h4 class="title">41.13.2.1. Implicit Rollback after Exceptions <a href="#PLPGSQL-PORTING-EXCEPTIONS" class="id_link">#</a></h4></div></div></div><p>
 391      In <span class="application">PL/pgSQL</span>, when an exception is caught by an
 392      <code class="literal">EXCEPTION</code> clause, all database changes since the block's
 393      <code class="literal">BEGIN</code> are automatically rolled back.  That is, the behavior
 394      is equivalent to what you'd get in Oracle with:
 395
 396 </p><pre class="programlisting">
 397 BEGIN
 398     SAVEPOINT s1;
 399     ... code here ...
 400 EXCEPTION
 401     WHEN ... THEN
 402         ROLLBACK TO s1;
 403         ... code here ...
 404     WHEN ... THEN
 405         ROLLBACK TO s1;
 406         ... code here ...
 407 END;
 408 </pre><p>
 409
 410      If you are translating an Oracle procedure that uses
 411      <code class="command">SAVEPOINT</code> and <code class="command">ROLLBACK TO</code> in this style,
 412      your task is easy: just omit the <code class="command">SAVEPOINT</code> and
 413      <code class="command">ROLLBACK TO</code>.  If you have a procedure that uses
 414      <code class="command">SAVEPOINT</code> and <code class="command">ROLLBACK TO</code> in a different way
 415      then some actual thought will be required.
 416     </p></div><div class="sect3" id="PLPGSQL-PORTING-OTHER-EXECUTE"><div class="titlepage"><div><div><h4 class="title">41.13.2.2. <code class="command">EXECUTE</code> <a href="#PLPGSQL-PORTING-OTHER-EXECUTE" class="id_link">#</a></h4></div></div></div><p>
 417      The <span class="application">PL/pgSQL</span> version of
 418      <code class="command">EXECUTE</code> works similarly to the
 419      <span class="application">PL/SQL</span> version, but you have to remember to use
 420      <code class="function">quote_literal</code> and
 421      <code class="function">quote_ident</code> as described in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="41.5.4. Executing Dynamic Commands">Section 41.5.4</a>.  Constructs of the
 422      type <code class="literal">EXECUTE 'SELECT * FROM $1';</code> will not work
 423      reliably unless you use these functions.
 424     </p></div><div class="sect3" id="PLPGSQL-PORTING-OPTIMIZATION"><div class="titlepage"><div><div><h4 class="title">41.13.2.3. Optimizing <span class="application">PL/pgSQL</span> Functions <a href="#PLPGSQL-PORTING-OPTIMIZATION" class="id_link">#</a></h4></div></div></div><p>
 425      <span class="productname">PostgreSQL</span> gives you two function creation
 426      modifiers to optimize execution: <span class="quote">“<span class="quote">volatility</span>”</span> (whether
 427      the function always returns the same result when given the same
 428      arguments) and <span class="quote">“<span class="quote">strictness</span>”</span> (whether the function
 429      returns null if any argument is null).  Consult the <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>
 430      reference page for details.
 431     </p><p>
 432      When making use of these optimization attributes, your
 433      <code class="command">CREATE FUNCTION</code> statement might look something
 434      like this:
 435
 436 </p><pre class="programlisting">
 437 CREATE FUNCTION foo(...) RETURNS integer AS $$
 438 ...
 439 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
 440 </pre><p>
 441     </p></div></div><div class="sect2" id="PLPGSQL-PORTING-APPENDIX"><div class="titlepage"><div><div><h3 class="title">41.13.3. Appendix <a href="#PLPGSQL-PORTING-APPENDIX" class="id_link">#</a></h3></div></div></div><p>
 442     This section contains the code for a set of Oracle-compatible
 443     <code class="function">instr</code> functions that you can use to simplify
 444     your porting efforts.
 445    </p><a id="id-1.8.8.15.8.3" class="indexterm"></a><pre class="programlisting">
 446 --
 447 -- instr functions that mimic Oracle's counterpart
 448 -- Syntax: instr(string1, string2 [, n [, m]])
 449 -- where [] denotes optional parameters.
 450 --
 451 -- Search string1, beginning at the nth character, for the mth occurrence
 452 -- of string2.  If n is negative, search backwards, starting at the abs(n)'th
 453 -- character from the end of string1.
 454 -- If n is not passed, assume 1 (search starts at first character).
 455 -- If m is not passed, assume 1 (find first occurrence).
 456 -- Returns starting index of string2 in string1, or 0 if string2 is not found.
 457 --
 458
 459 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
 460 BEGIN
 461     RETURN instr($1, $2, 1);
 462 END;
 463 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
 464
 465
 466 CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
 467                       beg_index integer)
 468 RETURNS integer AS $$
 469 DECLARE
 470     pos integer NOT NULL DEFAULT 0;
 471     temp_str varchar;
 472     beg integer;
 473     length integer;
 474     ss_length integer;
 475 BEGIN
 476     IF beg_index &gt; 0 THEN
 477         temp_str := substring(string FROM beg_index);
 478         pos := position(string_to_search_for IN temp_str);
 479
 480         IF pos = 0 THEN
 481             RETURN 0;
 482         ELSE
 483             RETURN pos + beg_index - 1;
 484         END IF;
 485     ELSIF beg_index &lt; 0 THEN
 486         ss_length := char_length(string_to_search_for);
 487         length := char_length(string);
 488         beg := length + 1 + beg_index;
 489
 490         WHILE beg &gt; 0 LOOP
 491             temp_str := substring(string FROM beg FOR ss_length);
 492             IF string_to_search_for = temp_str THEN
 493                 RETURN beg;
 494             END IF;
 495
 496             beg := beg - 1;
 497         END LOOP;
 498
 499         RETURN 0;
 500     ELSE
 501         RETURN 0;
 502     END IF;
 503 END;
 504 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
 505
 506
 507 CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
 508                       beg_index integer, occur_index integer)
 509 RETURNS integer AS $$
 510 DECLARE
 511     pos integer NOT NULL DEFAULT 0;
 512     occur_number integer NOT NULL DEFAULT 0;
 513     temp_str varchar;
 514     beg integer;
 515     i integer;
 516     length integer;
 517     ss_length integer;
 518 BEGIN
 519     IF occur_index &lt;= 0 THEN
 520         RAISE 'argument ''%'' is out of range', occur_index
 521           USING ERRCODE = '22003';
 522     END IF;
 523
 524     IF beg_index &gt; 0 THEN
 525         beg := beg_index - 1;
 526         FOR i IN 1..occur_index LOOP
 527             temp_str := substring(string FROM beg + 1);
 528             pos := position(string_to_search_for IN temp_str);
 529             IF pos = 0 THEN
 530                 RETURN 0;
 531             END IF;
 532             beg := beg + pos;
 533         END LOOP;
 534
 535         RETURN beg;
 536     ELSIF beg_index &lt; 0 THEN
 537         ss_length := char_length(string_to_search_for);
 538         length := char_length(string);
 539         beg := length + 1 + beg_index;
 540
 541         WHILE beg &gt; 0 LOOP
 542             temp_str := substring(string FROM beg FOR ss_length);
 543             IF string_to_search_for = temp_str THEN
 544                 occur_number := occur_number + 1;
 545                 IF occur_number = occur_index THEN
 546                     RETURN beg;
 547                 END IF;
 548             END IF;
 549
 550             beg := beg - 1;
 551         END LOOP;
 552
 553         RETURN 0;
 554     ELSE
 555         RETURN 0;
 556     END IF;
 557 END;
 558 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
 559
 560 </pre></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-development-tips.html" title="41.12. Tips for Developing in PL/pgSQL">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pltcl.html" title="Chapter 42. PL/Tcl — Tcl Procedural Language">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.12. Tips for Developing in <span class="application">PL/pgSQL</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 42. PL/Tcl — Tcl Procedural Language</td></tr></table></div></body></html>