This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port
applications from Oracle® to
PostgreSQL.

PL/pgSQL 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 PL/SQL to
PL/pgSQL are:

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,
PL/SQL treats it as a column
name. This corresponds to PL/pgSQL's plpgsql.variable_conflict = use_column behavior, which is not the default,
as explained in Section
39.10.1. 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 variable_conflict may be the best
solution.

In PostgreSQL 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 Section
39.11.1.)

Instead of packages, use schemas to organize your
functions into groups.

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.

Integer FOR loops with REVERSE work differently: PL/SQL counts down from the second
number to the first, while PL/pgSQL 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 Section
39.6.3.5.)

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

There are various notational differences for the use of
cursor variables.

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

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

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

The show errors command does
not exist in PostgreSQL,
and is not needed since errors are reported
automatically.

Notice how the body of the function is built separately
and passed through quote_literal 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 referrer_key.key_string field. (We are
assuming here that referrer_key.kind can be trusted to always
be host, domain, or url, but
referrer_key.key_string 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 referrer_key.key_string or referrer_key.referrer_type contain quote
marks.

Example
39-9 shows how to port a function with OUT parameters and string manipulation.
PostgreSQL does not have a
built-in instr function, but you
can create one using a combination of other functions. In
Section
39.12.3 there is a PL/pgSQL implementation of instr that you can use to make your porting
easier.

Example 39-9. Porting a Procedure With String
Manipulation and OUT Parameters from
PL/SQL to PL/pgSQL

The following Oracle
PL/SQL procedure is used to parse a URL and return several
elements (host, path, and query).

You cannot issue COMMIT in a
PL/pgSQL function. The
function is running within some outer transaction and so
COMMIT would imply terminating
the function's execution. However, in this particular
case it is not necessary anyway, because the lock
obtained by the LOCK TABLE will
be released when we raise an error.

The exception names supported by PL/pgSQL are different from
Oracle's. The set of built-in exception names is much
larger (see Appendix
A). There is not currently a way to declare
user-defined exception names, although you can throw
user-chosen SQLSTATE values instead.

The main functional difference between this procedure
and the Oracle equivalent is that the exclusive lock on the
cs_jobs 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.

In PL/pgSQL, when an
exception is caught by an EXCEPTION
clause, all database changes since the block's BEGIN are automatically rolled back. That is,
the behavior is equivalent to what you'd get in Oracle
with:

If you are translating an Oracle procedure that uses
SAVEPOINT and ROLLBACK TO in this style, your task is easy:
just omit the SAVEPOINT and
ROLLBACK TO. If you have a procedure
that uses SAVEPOINT and ROLLBACK TO in a different way then some
actual thought will be required.

The PL/pgSQL version of
EXECUTE works similarly to the
PL/SQL version, but you have
to remember to use quote_literal and quote_ident as described in Section
39.5.4. Constructs of the type EXECUTE 'SELECT * FROM $1'; will not work
reliably unless you use these functions.

PostgreSQL gives you two
function creation modifiers to optimize execution:
"volatility" (whether the function
always returns the same result when given the same arguments)
and "strictness" (whether the
function returns null if any argument is null). Consult the
CREATE FUNCTION
reference page for details.

When making use of these optimization attributes, your
CREATE FUNCTION statement might look
something like this:

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.