[DB-SIG] Would like to challenge DBAPI on callproc's return value

[Milosz Kosmider]

Hi folks,

As PostgreSQL has had named parameters in stored procedures since version
9.0, I
have taken to adding support for the feature in the DBAPI-complicant Python
wrapper, psycopg2: https://github.com/psycopg/psycopg2/pull/16/files.

The feature allows the "callproc" method to be invoked, in addition to the
standard, tuple-y way, like this: cur.callproc('some_proc', { some_param:
'some_value', ... })

While implementing the feature I stumbled upon the DBAPI spec for callproc:
http://legacy.python.org/dev/peps/pep-0249/#callproc. It states that "the
result
of the call is returned as modified copy of the input sequence. Input
parameters
are left untouched, output and input/output parameters replaced with
possibly
new values." It then goes onto say that "The procedure may also provide a
result
set as output."

I would like to challenge the former requirement, and make the latter
statement a strict requirement.

The former requirement of returning the modified input sequence effectively
forces cur.callproc to examine its result set and copy it over the input
sequence. This is not only redundant, but in fact undefined for result sets
with
more than one row. The requirement's definition is moreover
self-contradictory,
because output parameters are not *in* the input sequence.

The aforementioned result set is a sufficient and intuitive tool for
extracting
data from a stored procedure call. As mechanisms for returning data, stored
procedures are not very different from queries. All queries yield result
sets.
All stored procedures yield result sets. Some queries yield empty result
sets.
Some stored procedures yield empty result sets. As such, I would argue that,
like a query (e.g. cur.execute on a SELECT statement) cur.callproc *must*
endow
the cursor with a result set.

I propose changing the DBAPI spec on callproc to be as follows:

(This method is optional since not all databases provide stored
procedures.)

Call a stored database procedure with the given name. The sequence of
parameters
must contain one entry for each argument that the procedure expects.
Overloaded
procedures are supported. If the underlying database supports named
parameters
in stored procedures, the sequence of parameters may be given as a
dictionary-like object mapping parameter names to values.

The procedure must provide a result set as output. This is then made
available
through the standard fetch* methods.

Return values are not defined.

Thank you,
Milosz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20151201/6b5f5a54/attachment.html>