43.13. Porting from Oracle PL/SQL
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, and 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 an SQL command could be either a column name of a table used in the command or a reference to a variable of the function, PL/SQL treats it as a column name. By default, PL/pgSQL will throw an error complaining that the name is ambiguous. You can specify
plpgsql.variable_conflict
=use_column
to change this behavior to match PL/SQL, as explained in Section 43.11.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, settingvariable_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 43.12.1.)
Data type names often need translation. For example, in Oracle string values are commonly declared as being of type
varchar2
, which is a non-SQL-standard type. In PostgreSQL, use typevarchar
ortext
instead. Similarly, replace typenumber
withnumeric
, or use some other numeric data type if there's a more appropriate one.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 withREVERSE
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 43.6.5.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.
43.13.1. Porting Examples
Example 43.9 shows how to port a simple function from PL/SQL to PL/pgSQL.
Example 43.9. Porting a Simple Function from PL/SQL to PL/pgSQL
Here is an Oracle PL/SQL function:
Let's go through this function and see the differences compared to PL/pgSQL:
The type name
varchar2
has to be changed tovarchar
ortext
. In the examples in this section, we'll usevarchar
, buttext
is often a better choice if you do not need specific string length limits.The
RETURN
key word in the function prototype (not the function body) becomesRETURNS
in PostgreSQL. Also,IS
becomesAS
, and you need to add aLANGUAGE
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.
This is how this function would look when ported to PostgreSQL:
Example 43.10 shows how to port a function that creates another function and how to handle the ensuing quoting problems.
Example 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL
The following procedure grabs rows from a SELECT
statement and builds a large function with the results in IF
statements, for the sake of efficiency.
This is the Oracle version:
Here is how this function would end up in PostgreSQL:
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 43.11 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 43.13.3 there is a PL/pgSQL implementation of instr
that you can use to make your porting easier.
Example 43.11. 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).
This is the Oracle version:
Here is a possible translation into PL/pgSQL:
This function could be used like this:
Example 43.12 shows how to port a procedure that uses numerous features that are specific to Oracle.
Example 43.12. Porting a Procedure from PL/SQL to PL/pgSQL
The Oracle version:
This is how we could port this procedure to PL/pgSQL:
43.13.2. Other Things to Watch For
This section explains a few other things to watch for when porting Oracle PL/SQL functions to PostgreSQL.
43.13.2.1. Implicit Rollback After Exceptions
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.
43.13.2.2. EXECUTE
EXECUTE
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 43.5.4. Constructs of the type EXECUTE 'SELECT * FROM $1';
will not work reliably unless you use these functions.
43.13.2.3. Optimizing PL/PgSQL 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:
43.13.3. Appendix
This section contains the code for a set of Oracle-compatible instr
functions that you can use to simplify your porting efforts.