Re: SQL-Invoked Procedures for 8.1

Gavin,

> I agree that packages give us something like classes in that we can define
> related functions/procs into a single namespace. They provide other
> features like package level variables and public/private functionality. I
> think they major use is namespacing, however, and we can more or less have
> that for free with schemas.

Don't knock non-namespacing aspects. Now that exception handling inside
functions/procedures will soon be possible, it will become very attractive to
hand off all exception handling in a "package" to a single error-handling
routine. Also, the namespacing itself is non-trivial for financial
applications built on SPs; when you have 1100 SPs, you need an additional
level of namespacing to organize them all ("nested schema" would serve this
as well, but are non-spec).

But, to argue against myself -- some of the aspects of packages are just
re-tracing the history of programming with SQL-script languages. In many
ways, it would make more sense to enhance PL/Perl and PL/Java|J etc. to allow
them to bring to bear their entire apparatus of OO/exception
handling/variables etc, than to re-create a subset of this functionality in
PL/pgSQL. Now that PL/perlNG is underway, I myself am considering migrating
large quantities of PL/pgSQL code to PL/perl.

But even for these "extension" languages, it would be useful to offer a
packaging construct, for organization if for nothing else.

So what am I saying? That we don't want to implement SPs in such a way that
would *prevent* the implementation of packages, but at the same time don't
want to make packages the focus of SPs, at least not yet.

> Good point. Neil and I have been nutting out some of the issues to do with
> allowing SPs to start up 'outside' of a transaction. There are some pretty
> weird cases like, what if a function calls a procedure? What if that
> function is called in the WHERE clause of a query?

Well, there's two possibilites that suggest themselves immediately to me:
1) Allow SPs to call Functions but not vice-versa.
2) For "multi-transactional" SPs, require a flag ("WITH TRANSACTIONS") which
then prevents the SP from being called by any Function.

Despite the limitations it would impose, I actually favor (1). It's far less
complicated than checking for flags at every turn. If we were to do (2),
there's always the possibility of a query calling a function which calls a
single-transaction procedure which calls a multi-transaction procedure, and
who wants to follow up all those chains?

> ANSI SQL has a flag, MODIFIES SQL DATA, which allows you to differentiate
> between SPs which affect the database (and therefore may do something
> which needs to be cleaned up in case of error) and those which don't --
> ie, they just operate on their arguments.

IMHO, this is just another case of the ANSI committee completely failing to
distinguish between SPs and Functions. What would be the point of an SP
that didn't act on the database? Why not just use a function?

From my perspective, the issue of Transactions *is* the fundamental defining
difference between SPs and Functions. The issue of return values and INOUT
parameters are just refinements of this. Functions are "meant" to do
limited processing of data to return a value in the context of a statement;
SPs are "meant" to run independant programs to manipulate the database,
outside of any query.

The fact that I (and many others) often use Functions like SPs is a reflection
of the lack of separate SPs in PostgreSQL and not because I don't think there
should be a distinction.

> I'm not sure about named parameter notation (as oracle calls it) for the
> arguements. It seems, at least to me, that it would encourage bad
> programming but if we want to ease migration it may be worthwhile. Does
> anyone know how widely the feature is used?

I'm not sure how widely it's used with Oracle. It's used very widely with
MSSQL, though.

> I like the efficiency of returning them after the CALL (after every
> query?). But what if someone declares a very large text variable. Do we
> need to return it every time?

I'd say yes. If this is a problem for the user, re-write the SP. Also, that
you said "after every query" shows that you're still thinking of SPs as
identical to Functions. ;-) Presumably, an SP with an OUT param including
8k of text would not be CALLed very often.

--
Josh Berkus
Aglio Database Solutions
San Francisco