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

Milosz Kosmider milosz at milosz.ca
Tue Dec 1 00:40:12 EST 2015

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
of the call is returned as modified copy of the input sequence. Input
are left untouched, output and input/output parameters replaced with
new values." It then goes onto say that "The procedure may also provide a
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
more than one row. The requirement's definition is moreover
because output parameters are not *in* the input sequence.

The aforementioned result set is a sufficient and intuitive tool for
data from a stored procedure call. As mechanisms for returning data, stored
procedures are not very different from queries. All queries yield result
All stored procedures yield result sets. Some queries yield empty result
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*
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

Call a stored database procedure with the given name. The sequence of
must contain one entry for each argument that the procedure expects.
procedures are supported. If the underlying database supports named
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
through the standard fetch* methods.

Return values are not defined.

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

More information about the DB-SIG mailing list