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