Oracle s PLSQL and PLpgSQLComparing Two Worlds

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.