Re: Stored procedures and out parameters - Mailing list pgsql-hackers

Peter,

I think this is all coming from Microsoft.  The JDBC driver API was
modeled after the ODBC API, and the ODBC specification also contains the
{call} escape.  Microsoft SQL Server is also the only SQL implementation
to handle this stored function/procedure stuff totally differently: They
only have procedures, but they return values, and they are invoked by an
EXEC command.  (They don't support transaction control AFAIK.)  The .NET
stuff is obviously also from Microsoft.

So from Microsoft's perspective, this makes some sense: They only have
one invokable object type, and their invocation syntax is different from
everyone else's.  So they made a compatibility wrapper in their client
libraries.

Everyone else, however, has two invokable object types and standard ways
to invoke them.  And they all seemingly faced this problem of how to jam
these two into this one hole provided by the JDBC spec and ended up with
slightly different, and incompatible, solutions.

I think, if you want to write a portable-sans-Microsoft JDBC
application, you can just run CALL or SELECT directly.  If you want to
write something that is compatible with Microsoft, you can map {call} to
a function invocation as before, which is actually more similar to a
procedure in MS SQL Server.

Am going to repeat some of Vladimir's responses here...

I don't really know (or care much) about the history of how language database APIs evolved to where they are, I'm more concerned with what the introduction of stored procedures will do... The problem we're describing seems to go beyond JDBC or .NET. Looking at psycopg, for example, there's a callproc() function that internally translates to SELECT * FROM (http://www.postgresqltutorial.com/postgresql-python/call-stored-procedures/) - at the very least there are going to be some very confused users when callproc() becomes a way to only invoke functions, whereas calling procedures requires something else. I don't think there's anything really Microsoft-specific about any of this (except maybe in the history) - just like JDBC and psycopg, there's simply a single standard way in the database API for invoking server-side things, and not two ways.

It's true that users will always be able to simply avoid the standard API altogether and do SELECT * FROM func() or CALL proc(), but it really isn't ideal to force users down this road, which once again, hurts portability and general adoption.

Andres,

> Are you actually suggesting we effectively drop procedure soupport?

The ideal solution here is to allow functions to be invoked with CALL, rather than rolling back the entire feature (which obviously nobody wants). This would allow drivers to simply change their API implementation to translate to CALL instead of SELECT * FROM. I have no idea what the risk of that is, what it would entail etc. - I'm just expressing the driver writer perspective here with Vladimir. Hopefully some satisfactory solution can be found here.