Home Explore Blog CI



postgresql

72th chunk of `doc/src/sgml/plpgsql.sgml`
2b65fd3661b56de54697b6d94c98e65ace3e4b3b93a7b01d0000000100000fa5
 requiring the loop bounds
       to be swapped when porting.  This incompatibility is unfortunate
       but is unlikely to be changed. (See <xref
       linkend="plpgsql-integer-for"/>.)
      </para>
     </listitem>

     <listitem>
      <para>
       <command>FOR</command> loops over queries (other than cursors) also work
       differently: the target variable(s) must have been declared,
       whereas <application>PL/SQL</application> always declares them implicitly.
       An advantage of this is that the variable values are still accessible
       after the loop exits.
      </para>
     </listitem>

     <listitem>
      <para>
       There are various notational differences for the use of cursor
       variables.
      </para>
     </listitem>

    </itemizedlist>
   </para>

  <sect2 id="plpgsql-porting-examples">
   <title>Porting Examples</title>

   <para>
    <xref linkend="pgsql-porting-ex1"/> shows how to port a simple
    function from <application>PL/SQL</application> to <application>PL/pgSQL</application>.
   </para>

   <example id="pgsql-porting-ex1">
    <title>Porting a Simple Function from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>

    <para>
     Here is an <productname>Oracle</productname> <application>PL/SQL</application> function:
<programlisting>
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                  v_version varchar2)
RETURN varchar2 IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;
</programlisting>
    </para>

    <para>
     Let's go through this function and see the differences compared to
     <application>PL/pgSQL</application>:

     <itemizedlist>
      <listitem>
       <para>
        The type name <type>varchar2</type> has to be changed to <type>varchar</type>
        or <type>text</type>.  In the examples in this section, we'll
        use <type>varchar</type>, but <type>text</type> is often a better choice if
        you do not need specific string length limits.
       </para>
      </listitem>

      <listitem>
       <para>
        The <literal>RETURN</literal> key word in the function
        prototype (not the function body) becomes
        <literal>RETURNS</literal> in
        <productname>PostgreSQL</productname>.
        Also, <literal>IS</literal> becomes <literal>AS</literal>, and you need to
        add a <literal>LANGUAGE</literal> clause because <application>PL/pgSQL</application>
        is not the only possible function language.
       </para>
      </listitem>

      <listitem>
       <para>
        In <productname>PostgreSQL</productname>, 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 <literal>/</literal>
        in the Oracle approach.
       </para>
      </listitem>

      <listitem>
       <para>
        The <literal>show errors</literal> command does not exist in
        <productname>PostgreSQL</productname>, and is not needed since errors are
        reported automatically.
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     This is how this function would look when ported to
     <productname>PostgreSQL</productname>:

<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;
</programlisting>
    </para>
   </example>

   <para>
    <xref linkend="plpgsql-porting-ex2"/> shows how to port a
    function that creates another function and how to handle the
    ensuing quoting problems.
   </para>

   <example id="plpgsql-porting-ex2">
    <title>Porting a Function that Creates Another

Title: PL/SQL to PL/pgSQL Porting Examples: Simple Function and Function Creation
Summary
This section provides practical examples of porting PL/SQL functions to PL/pgSQL. It demonstrates the process with a simple function, highlighting differences in data types (varchar2 to varchar/text), return type declaration (RETURN to RETURNS, IS to AS), the need for a LANGUAGE clause, string literal quoting, and error reporting. The section then begins to discuss porting a more complex function that creates another function and introduces the challenge of handling nested quoting.