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
basedonmisinformation, which would be good to correct.
 

First, some prefaces:  PL/pgSQL does not currently have a real devotedproject head.  It was mostly the brainchild of
JanWieck, who Ibelieve developed it as a "side effect" of creating PL/pgTCL.  So oneof the reasons that the capabilites
ofPL/pgSQL have been limited isthat nobody with the required skills has stepped forward from thecommunity to take
PL/pgSQLto the next stage of development.  The 6core developers are a little busy.
 

Second, with the robustness of Java, J2EE, C++, and Perl::DBI, Ibelieve that it has long been assumed by the core
developersand amajority of the community that any large application would beprogrammed using a seperate middleware
langaugeand full-blown n-tierdevelopment.  Thus, for a lot of people, if PL/pgSQL is adequate forcomplex triggers and
rules,it is sufficient; if you need incapsulatedbusiness logic, use Perl or Java.
 

I'm not putting this forward as what I necessarily believe in, but thelogic that drives the current "lightweight"
natureof PL/pgSQL ascompared with PL/SQL.  It's an open-source project, though ... hire aC programmer and you can
changethat.
 

>  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 theycan't handle using mailing lists as your knowledge
base,IMNSHO theyhave 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 oftargeting MS SQL Server as their main
competitor,something they paidthe price for.  I still reccommend Oracle to some (but very few) of mycustomers who need
someof the add-ons that come with Oracle and havemore 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 goodrapid development approach for intranet apps, and
relativelysecure. I just don't try to get PL/pgSQL to do anything it can't, and do myerror 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 wouldhappily give you all of his PL/pgSQL development notes
sothat you canexpand 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 thePostgres team's shortage of skilled manpower.
Wannadonate aprogrammer?  I'd love to see cursor and error handling in PL/pgSQLimproved, and I can't think that anybody
wouldobject.
 

> 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/pgSQLis already miles ahead of Transact-SQL.  Further,
Microsoftis notimproving the procedural elements of T-SQL in new versions because MSwants you to use .NET objects and
notstored procedures that might beportable to another platform.  Perhaps more importantly, MS did notwrite T-SQL
(Sybasedid), 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 todonate resources so that we can continue to offer a
greatdatabase(IMHO, better than anything but Oracle and better than Oracle on acouple of issues) for free.  At a
minimum,that participation mustinclude providing detailed and well-considered requests for changes. Contributing code,
documentation,and/or money is better and morelikely to realize your goals.
 

Your post is extremely useful, and will no doubt be seized upon by RedHat as strategic to their RHDB program if they
knowwhat's good forthem.  However, it's a mistake to regard the Postgres project as if itwas a vendor, from whom one
expectsprogram improvements just becauseone is a good customer. 
 

Frankly, considering the Oracle DBAs you refer to who can't even bebothered to join the mailing list ... I, for one,
don'twant them aspart of the Postgres product and don't feel that there is any reasonfor the Postgres developers to
considertheir needs.    
 

For anyone else who is lurking on the mailing list, though ... SPEAKUP!  nobody will address your needs if you never
communicatethem.
 

-Josh Berkus