Re: 16 parameter limit

John,

You bring up some interesting points. I agree with you in some parts,
but some of your difficulties with PL/pgSQL are based on
misinformation, which would be good to correct.

First, some prefaces: PL/pgSQL does not currently have a real devoted
project head. It was mostly the brainchild of Jan Wieck, who I
believe developed it as a "side effect" of creating PL/pgTCL. So one
of the reasons that the capabilites of PL/pgSQL have been limited is
that nobody with the required skills has stepped forward from the
community to take PL/pgSQL to the next stage of development. The 6
core developers are a little busy.

Second, with the robustness of Java, J2EE, C++, and Perl::DBI, I
believe that it has long been assumed by the core developers and a
majority of the community that any large application would be
programmed using a seperate middleware langauge and full-blown n-tier
development. Thus, for a lot of people, if PL/pgSQL is adequate for
complex triggers and rules, it is sufficient; if you need incapsulated
business logic, use Perl or Java.

I'm not putting this forward as what I necessarily believe in, but the
logic that drives the current "lightweight" nature of PL/pgSQL as
compared with PL/SQL. It's an open-source project, though ... hire a
C programmer and you can change 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.

Yes, but we're not going to interest those people anyway. If they
can't handle using mailing lists as your knowledge base, IMNSHO they
have no place in the Open Source world. Stick to expensive,
well-documented proprietary products.

> I think that the limitations of PL/pgSQL is a huge factor in people
> not being
> able to use Postgres instead of Oracle.

See above. IMHO, Great Bridge was mistaken to target Oracle instead of
targeting MS SQL Server as their main competitor, something they paid
the price for. I still reccommend Oracle to some (but very few) of my
customers who need some of the add-ons that come with Oracle and have
more money than time.

> 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.

I've done this on a smaller scale with Postgres + PHP. It's a good
rapid development approach for intranet apps, and relatively secure.
I just don't try to get PL/pgSQL to do anything it can't, and do my
error handling in interface code.

> 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.

If you feel strongly enough about this, I am sure that Jan would
happily give you all of his PL/pgSQL development notes so that you can
expand the language.

> 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.

PL/pgSQL has had parameter aliases since Postgres 7.0.0.

> 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.

This is a singnificant failing. Once again, I can only point out the
Postgres team's shortage of skilled manpower. Wanna donate a
programmer? I'd love to see cursor and error handling in PL/pgSQL
improved, and I can't think that anybody would object.

> It also seems that Microsoft SQLServer shops are
> moving
> in the same direction now that the procedural support for it is
> getting much
> better.

Here, I disagree. I am a certified MS SQL Server admin, and PL/pgSQL
is already miles ahead of Transact-SQL. Further, Microsoft is not
improving the procedural elements of T-SQL in new versions because MS
wants you to use .NET objects and not stored procedures that might be
portable to another platform. Perhaps more importantly, MS did not
write T-SQL (Sybase did), and as a result has trouble modifying it.

> 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.

Yes, but without the posts, we don't know what's wrong, now, do we?

Postgres is an Open Source project. We depend on the community to
donate resources so that we can continue to offer a great database
(IMHO, better than anything but Oracle and better than Oracle on a
couple of issues) for free. At a minimum, that participation must
include providing detailed and well-considered requests for changes.
Contributing code, documentation, and/or money is better and more
likely to realize your goals.

Your post is extremely useful, and will no doubt be seized upon by Red
Hat as strategic to their RHDB program if they know what's good for
them. However, it's a mistake to regard the Postgres project as if it
was a vendor, from whom one expects program improvements just because
one is a good customer.

Frankly, considering the Oracle DBAs you refer to who can't even be
bothered to join the mailing list ... I, for one, don't want them as
part of the Postgres product and don't feel that there is any reason
for the Postgres developers to consider their needs.

For anyone else who is lurking on the mailing list, though ... SPEAK
UP! nobody will address your needs if you never communicate them.

-Josh Berkus