16 parameter limit

There was a message posted in March regarding this. Bruce replied that this
issue did not come up often. However, I think there is more to it than
that. I think one reason that it does not come up is because most Oracle
DBAs are not going to dig through mailing lists and take the time to post
questions. Once they discover that PL/pgSQL != PL/SQL they just move on.

I think that the limitations of PL/pgSQL is a huge factor in people not being
able to use Postgres instead of Oracle. My company is quite small, but we
have several very large insurance companies for clients that we develop web
based applications for. Currently I have 5 schemas totaling about 1500
tables and about as many stored procedures and functions. The applications
do not even have any permissions on a single table. All selects are done on
views and all inserts/updates/deletes are done through stored procedures.
Our procs have many parameters, one per column or more. Most of the app
developers do not even know that much about the schema. They just know the
exposed procedural interface.

Other issues similar to this with regards to PL/SQL are the need for packages
and the ability to declare cursors ahead of time, like in a package so that
they can be shared and opened when needed. This also makes much cleaner
code since the select statement for many cursors clouds the code where it is
used if it is inline like PL/pgSQL.

Named parameters would also be nice and at least allowing the use of giving
names to parameters in the declarations instead of $1, $2, etc.

Also, the inablity to trap database "exceptions" is too limiting. In
Oracle, we trap every single exception, start an autonomous transacation, log
the exception to an exception table along with the procedure name, the
operation being performed and some marker to mke it easy to locate the
offending statement. This also allows us to recover, which is very important
for imports and data loads.

I work with many other Oracle DBAs and I think many have interest in
Postgres, but also know that without a procedural language on par with PL/SQL
that it is not possible to switch. All of the Oracle shops that I know of
are very big on PL/SQL and write almost all business logic and table
interfaces in it. It also seems that Microsoft SQLServer shops are moving
in the same direction now that the procedural support for it is getting much
better.

I am not complaining about Postgres at all. I think it is fantastic and I
enjoy using it for personal projects. However, I think it might be a bit
misleading to assume that lack of posts regarding the limits of PL/pgSQL
equate to it being adequate for most large applications. It is the number
one reason that I could not use Postgres in 4 large insurance companies.

John Proctor