Re: Wishlist? - Mailing list pgsql-general

On Mon, Jul 25, 2005 at 11:35:14AM +1000, Ezequiel Tolnay wrote:
> Functions are not the same as stored procedures, but since PG lacks
> stored procedures, there is a necessity to use functions instead.

Ok, maybe I'm missing something, but the only difference between a
procedure and a function is that a function returns a value and a
procedure doesn't. There's no difference in capabilities so I don't
really understand the problem here.

> Stored procedures (in the RDBMSs that support them) are normally used to
>  1) run batch processes, and 2) separate the interface from the
> database logic (by interface, I mean an interface with the client app).
> Functions on the other side are very convenient as a kind of
> parameterisable or extended view, and (if there are stored procedures
> available) not really intended as an interface with the front-end.

But AFAICS this is a distinction made by people (like in Pascal) but is
not a distinction at all. As far as the system is concerned they are
identical. Everything you can do in one of those "procedures" you can
do in PostgreSQL now, so what is the problem?

> One of the first hassles with using functions as an interface (mainly
> with web-based) front-ends is that, despite de fact that you'd normally
> never (or very rarely) use or require overloading, you're still required
> to cast every parameter to match that of the function. And when you
> have, say, 50 parameters (used by a web form to create, for instance, a
> customer, with information spread around many tables), if you miss
> casting the type on one of them (e.g. to int2 if that's the way it's
> been defined) you get an error.

Only if you're using an old version of postgres. Anyway, if you always
quote all your arguments this becomes a complete non-issue... In fact,
it will then complain if you have overloaded your function...

> Another is that if you cannot use CREATE OR REPLACE to change one of the
> parameters, thus having to drop and recreate, and this requires setting
> all the permissions all over again.

Well, you have to do this anyway and any dependant objects and
currently cached query plans, etc would all have to be killed. This
would be solvable I guess, if there was a concensus to do it.

> Another one would be the impossibility to implement parameter defaults,
> and the chance to provide some of the values in a different order than
> that of the function's definition, by specifying the parameter names (a
> feature already present in the TODO list) and letting the function use
> the defaults for the missing parameters.

These are on the todo list and have nothing to do with function
overloading AFAIK.

> Anyway, this is not about being annoying, I think it would be great if
> PG had not only its own special and unique features, but also all the
> common features you would find in other RDBMSs, for portability and
> convenience.

True, there is a tendancy to Oracle on things that aren't standardised.
I'm not sure how much of all this is supported in Oracle though.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.