Porting Oracle database
Porting from Oracle PL/SQL
This section explains differences between Oracle's PL/SQL and PostgreSQL's PL/pgSQL languages in the hopes of helping developers port applications from Oracle to PostgreSQL.
PL/pgSQL is similar to PL/SQL in many aspects. It is a block structured, imperative language (all variables have to be declared). PL/SQL has many more features than its PostgreSQL counterpart, but PL/pgSQL allows for a great deal of functionality and it is being improved constantly.
Main Differences
Some things you should keep in mind when porting from Oracle to PostgreSQL:
-
No default parameters in PostgreSQL.
-
You can overload functions in PostgreSQL. This is often used to work around the lack of default parameters.
-
Assignments, loops and conditionals are similar.
-
No need for cursors in PostgreSQL, just put the query in the FOR statement (see example below)
-
In PostgreSQL you need to escape single
-
Escaping Single Quotes
-
In PostgreSQL you need to escape single quotes inside your function definition. This can lead to quite amusing code at times, especially if you are creating a function that generates other function(s). One thing to keep in mind when escaping lots of single quotes is that, except for the beginning/ending quotes, all the others will come in even quantity.