Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
plpgsql-porting.html560 linesDownload Raw Back to 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>43.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="43.12. Tips for Developing in PL/pgSQL" /><link rel="next" href="pltcl.html" title="Chapter 44. 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">43.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="43.12. Tips for Developing in PL/pgSQL">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pltcl.html" title="Chapter 44. 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">43.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">43.13.1. Porting Examples</a></span></dt><dt><span class="sect2"><a href="plpgsql-porting.html#PLPGSQL-PORTING-OTHER">43.13.2. Other Things to Watch For</a></span></dt><dt><span class="sect2"><a href="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX">43.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 between4   <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 from7   <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 many10   aspects. It is a block-structured, imperative language, and all11   variables have to be declared.  Assignments, loops, and conditionals12   are similar.  The main differences you should keep in mind when13   porting from <span class="application">PL/SQL</span> to14   <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 a18       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 error21       complaining that the name is ambiguous.  You can specify22       <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="43.11.1. Variable Substitution">Section 43.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 on27       this behavior, setting <code class="literal">variable_conflict</code> may be the28       best solution.29      </p></li><li class="listitem"><p>30       In <span class="productname">PostgreSQL</span> the function body must be written as31       a string literal.  Therefore you need to use dollar quoting or escape32       single quotes in the function body. (See <a class="xref" href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS" title="43.12.1. Handling of Quotation Marks">Section 43.12.1</a>.)33      </p></li><li class="listitem"><p>34       Data type names often need translation.  For example, in Oracle string35       values are commonly declared as being of type <code class="type">varchar2</code>, which36       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, replace38       type <code class="type">number</code> with <code class="type">numeric</code>, or use some other numeric39       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 functions42       into groups.43      </p></li><li class="listitem"><p>44       Since there are no packages, there are no package-level variables45       either. This is somewhat annoying.  You can keep per-session state46       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> work49       differently: <span class="application">PL/SQL</span> counts down from the second50       number to the first, while <span class="application">PL/pgSQL</span> counts down51       from the first number to the second, requiring the loop bounds52       to be swapped when porting.  This incompatibility is unfortunate53       but is unlikely to be changed. (See <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR" title="43.6.5.5. FOR (Integer Variant)">Section 43.6.5.5</a>.)54      </p></li><li class="listitem"><p>55       <code class="command">FOR</code> loops over queries (other than cursors) also work56       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 accessible59       after the loop exits.60      </p></li><li class="listitem"><p>61       There are various notational differences for the use of cursor62       variables.63      </p></li></ul></div><p>64   </p><div class="sect2" id="PLPGSQL-PORTING-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">43.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 43.9. Porting a Simple Function from PL/SQL to PL/pgSQL">Example 43.9</a> shows how to port a simple66    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 43.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">70CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,71                                                  v_version varchar2)72RETURN varchar2 IS73BEGIN74    IF v_version IS NULL THEN75        RETURN v_name;76    END IF;77    RETURN v_name || '/' || v_version;78END;79/80show errors;81</pre><p>82    </p><p>83     Let's go through this function and see the differences compared to84     <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'll89        use <code class="type">varchar</code>, but <code class="type">text</code> is often a better choice if90        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 function93        prototype (not the function body) becomes94        <code class="literal">RETURNS</code> in95        <span class="productname">PostgreSQL</span>.96        Also, <code class="literal">IS</code> becomes <code class="literal">AS</code>, and you need to97        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 considered101        to be a string literal, so you need to use quote marks or dollar102        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 in106        <span class="productname">PostgreSQL</span>, and is not needed since errors are107        reported automatically.108       </p></li></ul></div><p>109    </p><p>110     This is how this function would look when ported to111     <span class="productname">PostgreSQL</span>:112 113</p><pre class="programlisting">114CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,115                                                  v_version varchar)116RETURNS varchar AS $$117BEGIN118    IF v_version IS NULL THEN119        RETURN v_name;120    END IF;121    RETURN v_name || '/' || v_version;122END;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 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 43.10</a> shows how to port a127    function that creates another function and how to handle the128    ensuing quoting problems.129   </p><div class="example" id="PLPGSQL-PORTING-EX2"><p class="title"><strong>Example 43.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 a131     <code class="command">SELECT</code> statement and builds a large function132     with the results in <code class="literal">IF</code> statements, for the133     sake of efficiency.134    </p><p>135     This is the Oracle version:136</p><pre class="programlisting">137CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS138    CURSOR referrer_keys IS139        SELECT * FROM cs_referrer_keys140        ORDER BY try_order;141    func_cmd VARCHAR(4000);142BEGIN143    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 LOOP147        func_cmd := func_cmd ||148          ' IF v_' || referrer_key.kind149          || ' LIKE ''' || referrer_key.key_string150          || ''' THEN RETURN ''' || referrer_key.referrer_type151          || '''; END IF;';152    END LOOP;153 154    func_cmd := func_cmd || ' RETURN NULL; END;';155 156    EXECUTE IMMEDIATE func_cmd;157END;158/159show 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">164CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$165DECLARE166    referrer_keys CURSOR IS167        SELECT * FROM cs_referrer_keys168        ORDER BY try_order;169    func_body text;170    func_cmd text;171BEGIN172    func_body := 'BEGIN';173 174    FOR referrer_key IN referrer_keys LOOP175        func_body := func_body ||176          ' IF v_' || referrer_key.kind177          || ' 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;193END;194$func$ LANGUAGE plpgsql;195</pre><p>196     Notice how the body of the function is built separately and passed197     through <code class="literal">quote_literal</code> to double any quote marks in it.  This198     technique is needed because we cannot safely use dollar quoting for199     defining the new function: we do not know for sure what strings will200     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 be202     trusted to always be <code class="literal">host</code>, <code class="literal">domain</code>, or203     <code class="literal">url</code>, but <code class="structfield">referrer_key.key_string</code> might be204     anything, in particular it might contain dollar signs.) This function205     is actually an improvement on the Oracle original, because it will206     not generate broken code when <code class="structfield">referrer_key.key_string</code> or207     <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 43.11. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to PL/pgSQL">Example 43.11</a> shows how to port a function210    with <code class="literal">OUT</code> parameters and string manipulation.211    <span class="productname">PostgreSQL</span> does not have a built-in212    <code class="function">instr</code> function, but you can create one213    using a combination of other214    functions. In <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX" title="43.13.3. Appendix">Section 43.13.3</a> there is a215    <span class="application">PL/pgSQL</span> implementation of216    <code class="function">instr</code> that you can use to make your porting217    easier.218   </p><div class="example" id="PLPGSQL-PORTING-EX3"><p class="title"><strong>Example 43.11. Porting a Procedure With String Manipulation and219    <code class="literal">OUT</code> Parameters from <span class="application">PL/SQL</span> to220    <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 used222     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">226CREATE OR REPLACE PROCEDURE cs_parse_url(227    v_url IN VARCHAR2,228    v_host OUT VARCHAR2,  -- This will be passed back229    v_path OUT VARCHAR2,  -- This one too230    v_query OUT VARCHAR2) -- And this one231IS232    a_pos1 INTEGER;233    a_pos2 INTEGER;234BEGIN235    v_host := NULL;236    v_path := NULL;237    v_query := NULL;238    a_pos1 := instr(v_url, '//');239 240    IF a_pos1 = 0 THEN241        RETURN;242    END IF;243    a_pos2 := instr(v_url, '/', a_pos1 + 2);244    IF a_pos2 = 0 THEN245        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 THEN254        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);260END;261/262show 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">267CREATE OR REPLACE FUNCTION cs_parse_url(268    v_url IN VARCHAR,269    v_host OUT VARCHAR,  -- This will be passed back270    v_path OUT VARCHAR,  -- This one too271    v_query OUT VARCHAR) -- And this one272AS $$273DECLARE274    a_pos1 INTEGER;275    a_pos2 INTEGER;276BEGIN277    v_host := NULL;278    v_path := NULL;279    v_query := NULL;280    a_pos1 := instr(v_url, '//');281 282    IF a_pos1 = 0 THEN283        RETURN;284    END IF;285    a_pos2 := instr(v_url, '/', a_pos1 + 2);286    IF a_pos2 = 0 THEN287        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 THEN296        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);302END;303$$ LANGUAGE plpgsql;304</pre><p>305 306     This function could be used like this:307</p><pre class="programlisting">308SELECT * 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 43.12. Porting a Procedure from PL/SQL to PL/pgSQL">Example 43.12</a> shows how to port a procedure312    that uses numerous features that are specific to Oracle.313   </p><div class="example" id="PLPGSQL-PORTING-EX4"><p class="title"><strong>Example 43.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">317CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS318    a_running_job_count INTEGER;319BEGIN320    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 THEN325        COMMIT; -- free lock326        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    BEGIN334        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());335    EXCEPTION336        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists337    END;338    COMMIT;339END;340/341show errors342</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">347CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$348DECLARE349    a_running_job_count integer;350BEGIN351    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 THEN356        COMMIT; -- free lock357        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    BEGIN364        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());365    EXCEPTION366        WHEN unique_violation THEN -- <span id="co.plpgsql-porting-exception"></span>(2)367            -- don't worry if it already exists368    END;369    COMMIT;370END;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 from376       Oracle's statement, although the basic case <code class="literal">RAISE</code>377       <em class="replaceable"><code>exception_name</code></em> works378       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> are381       different from Oracle's.  The set of built-in exception names382       is much larger (see <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>).  There383       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">43.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 porting388    Oracle <span class="application">PL/SQL</span> functions to389    <span class="productname">PostgreSQL</span>.390   </p><div class="sect3" id="PLPGSQL-PORTING-EXCEPTIONS"><div class="titlepage"><div><div><h4 class="title">43.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 an392     <code class="literal">EXCEPTION</code> clause, all database changes since the block's393     <code class="literal">BEGIN</code> are automatically rolled back.  That is, the behavior394     is equivalent to what you'd get in Oracle with:395 396</p><pre class="programlisting">397BEGIN398    SAVEPOINT s1;399    ... code here ...400EXCEPTION401    WHEN ... THEN402        ROLLBACK TO s1;403        ... code here ...404    WHEN ... THEN405        ROLLBACK TO s1;406        ... code here ...407END;408</pre><p>409 410     If you are translating an Oracle procedure that uses411     <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> and413     <code class="command">ROLLBACK TO</code>.  If you have a procedure that uses414     <code class="command">SAVEPOINT</code> and <code class="command">ROLLBACK TO</code> in a different way415     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">43.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 of418     <code class="command">EXECUTE</code> works similarly to the419     <span class="application">PL/SQL</span> version, but you have to remember to use420     <code class="function">quote_literal</code> and421     <code class="function">quote_ident</code> as described in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.  Constructs of the422     type <code class="literal">EXECUTE 'SELECT * FROM $1';</code> will not work423     reliably unless you use these functions.424    </p></div><div class="sect3" id="PLPGSQL-PORTING-OPTIMIZATION"><div class="titlepage"><div><div><h4 class="title">43.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 creation426     modifiers to optimize execution: <span class="quote">“<span class="quote">volatility</span>”</span> (whether427     the function always returns the same result when given the same428     arguments) and <span class="quote">“<span class="quote">strictness</span>”</span> (whether the function429     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, your433     <code class="command">CREATE FUNCTION</code> statement might look something434     like this:435 436</p><pre class="programlisting">437CREATE 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">43.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-compatible443    <code class="function">instr</code> functions that you can use to simplify444    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 counterpart448-- Syntax: instr(string1, string2 [, n [, m]])449-- where [] denotes optional parameters.450--451-- Search string1, beginning at the nth character, for the mth occurrence452-- of string2.  If n is negative, search backwards, starting at the abs(n)'th453-- 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 459CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$460BEGIN461    RETURN instr($1, $2, 1);462END;463$$ LANGUAGE plpgsql STRICT IMMUTABLE;464 465 466CREATE FUNCTION instr(string varchar, string_to_search_for varchar,467                      beg_index integer)468RETURNS integer AS $$469DECLARE470    pos integer NOT NULL DEFAULT 0;471    temp_str varchar;472    beg integer;473    length integer;474    ss_length integer;475BEGIN476    IF beg_index &gt; 0 THEN477        temp_str := substring(string FROM beg_index);478        pos := position(string_to_search_for IN temp_str);479 480        IF pos = 0 THEN481            RETURN 0;482        ELSE483            RETURN pos + beg_index - 1;484        END IF;485    ELSIF beg_index &lt; 0 THEN486        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 LOOP491            temp_str := substring(string FROM beg FOR ss_length);492            IF string_to_search_for = temp_str THEN493                RETURN beg;494            END IF;495 496            beg := beg - 1;497        END LOOP;498 499        RETURN 0;500    ELSE501        RETURN 0;502    END IF;503END;504$$ LANGUAGE plpgsql STRICT IMMUTABLE;505 506 507CREATE FUNCTION instr(string varchar, string_to_search_for varchar,508                      beg_index integer, occur_index integer)509RETURNS integer AS $$510DECLARE511    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;518BEGIN519    IF occur_index &lt;= 0 THEN520        RAISE 'argument ''%'' is out of range', occur_index521          USING ERRCODE = '22003';522    END IF;523 524    IF beg_index &gt; 0 THEN525        beg := beg_index - 1;526        FOR i IN 1..occur_index LOOP527            temp_str := substring(string FROM beg + 1);528            pos := position(string_to_search_for IN temp_str);529            IF pos = 0 THEN530                RETURN 0;531            END IF;532            beg := beg + pos;533        END LOOP;534 535        RETURN beg;536    ELSIF beg_index &lt; 0 THEN537        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 LOOP542            temp_str := substring(string FROM beg FOR ss_length);543            IF string_to_search_for = temp_str THEN544                occur_number := occur_number + 1;545                IF occur_number = occur_index THEN546                    RETURN beg;547                END IF;548            END IF;549 550            beg := beg - 1;551        END LOOP;552 553        RETURN 0;554    ELSE555        RETURN 0;556    END IF;557END;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="43.12. Tips for Developing in PL/pgSQL">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pltcl.html" title="Chapter 44. PL/Tcl — Tcl Procedural Language">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 44. PL/Tcl — Tcl Procedural Language</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai