Oracle's PL/SQL and PL/pgSQL ”Comparing Two Worlds
This section is
mainly
dedicated to those who want to port their PL/SQL functions from Oracle to PostgreSQL, but we also want to point out some major differences between the two languages to ensure that people make their decision of whether to use Oracle or PostgreSQL objectively.
PL/pgSQL and PL/pgSQL are similar in many respects. Not only are both languages block-oriented and directly executed by the database, PL/SQL and PL/pgSQL have many more things in common.
The Main Differences
Although PostgreSQL's PL/pgSQL does not have as many features as Oracle's PL/SQL, PL/pgSQL can be used to build functions with tremendous functionality. But there are major differences between the two.
Quoting
The most apparent difference is that the programmer needs to be careful with single quotes. This circumstance can lead to quite interesting pieces of code. Imagine a situation where two single quotes are used in the result:
CREATE FUNCTION testfunc() RETURNS text AS '
DECLARE
a text;
BEGIN
a := ''many quotes: \'\'\'\' '';
RETURN a;
END;
' LANGUAGE 'plpgsql';
Every quote in the output has to be masked using a backslash. Single quotes that are part of the function code have to be masked using two single quotes:
CREATE FUNCTION testfunc() RETURNS text AS '
BEGIN
RETURN ''t'';
END;
' LANGUAGE 'plpgsql';
To use one single quote in the PL/pgSQL function, we have to send two single quotes to the function in order to escape one single quote.
Function Overloading
PostgreSQL supports function overloading, which can be used as a workaround for the lack of default parameters. Function overloading is a powerful feature that fits well into PostgreSQL's object-oriented concepts. Keep in mind that
DROP FUNCTION
has to be called with the correct parameters;
otherwise
, the wrong function gets dropped.
PostgreSQL and Database
Cursors
Oracle's PL/SQL has sophisticated and highly developed concepts for cursors, which is quite an advantage over PL/pgSQL. In PostgreSQL, the lack of Oracle-like cursors can be worked around by using queries in combination with
FOR
loops. To refresh your memory, review the example of this in the section titled "Using LOOP/EXIT, WHILE, and FOR
Loops
," earlier in this chapter.
Creating and Replacing Functions
In Oracle, functions can easily be created and
replaced
by using
CREATE OR REPLACE FUNCTION
." In PostgreSQL, functions have to be dropped and created explicitly with
DROP FUNCTION functionname(datatype, ...)
and
CREATE FUNCTION
.
Making Life Easier with
instr
Oracle datatbases provide a function called
instr
, which is used to obtain the corresponding position of a pattern match in a given
FILE
or
LOB
. PL/pgSQL does not, unlike Oracle, offer an
instr()
function. This does not make life easier for PostgreSQL users, so some people have written various versions of
instr()
functions that can be found on the Internet.
Locking Tables
PostgreSQL supports sophisticated locking mechanisms. When a PL/pgSQL function locks a table, as in the following command, the lock is released when the PL/pgSQL function resumes:
LOCK TABLE employees IN EXCLUSIVE MODE;
Functions and Transactions
You can't have transactions in PL/pgSQL functions. Every function is executed in one transaction, and an implicit
ROLLBACK
is performed if something goes wrong inside the function. This protects both the database and the programmer if something dangerous happens inside a transaction.
|