<div dir="ltr"><div>As far as I can tell, the PG C API does not give direct access to stored procedures at all, instead only giving access via exec function with sanitized bound parameters. The current implementation of callproc in psycopg2 binds variables in a SELECT statement in order to execute a stored procedure, e.g. "SELECT some_procedure(...)". I took a look at py-postgresql, where equivalent functionality is implemented as db.proc, and there, too, a SELECT statement is used to invoke a stored procedure.</div><div><br></div><div>In fact, the way the API is described in <a href="http://www.postgresql.org/docs/9.1/static/libpq-exec.html">http://www.postgresql.org/docs/9.1/static/libpq-exec.html</a>, it would appear that C programmers are to communicate with PostgreSQL exclusively through text commands. Outright access to internal objects like stored procedures and tables is not given.</div><div><br></div><div>The solution in which the parameters tuple is returned directly works when a tuple is passed in, but would be difficult in the dictionary case. In the dictionary case, to return an ordered tuple, it would be necessary to query the database to determine the order of parameters for the matched overload of the given procedure, which would introduce some unwanted overhead.</div><div><br></div><div>One possible interpretation of the spec in the dictionary case would be to return the passed-in dictionary, but it wouldn't be a tuple as stated by the spec.</div><div><br></div><div>As I see it, there are two ways to implement the spec, both of which are not 100% compliant. The first is to return the parameters object, whether it is an ordered tuple or a dictionary. The second is to return Py_None. These are equally easy to deal with for the programmer, computer, and API user. I am leaning torwards Py_None because PG users don't expect a return value anyway. I will talk this over with the psycopg2 team and reference this email chain.</div><div><br></div><div>Cheers,</div><div>Milosz</div><br><div class="gmail_quote"><div dir="ltr">On Mon, Dec 7, 2015 at 1:35 PM M.-A. Lemburg <<a href="mailto:mal@egenix.com" target="_blank">mal@egenix.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 06.12.2015 20:10, Milosz Kosmider wrote:<br>
> Hi Marc-Andre, Mike,<br>
><br>
> Thanks for responding and setting me straight on the spec! Being that<br>
> PostgreSQL is a little bit unique in that it always returns its INOUT/OUT<br>
> parameters in the result set and provides no other way to access their<br>
> values, would it be an acceptable compromise for callproc to return a null<br>
> value when backed by PostgreSQL?<br>
><br>
> Even the solution of copying inputs to outputs is not trivial. PostgreSQL<br>
> procedures are invoked using the input parameters only, so one possible<br>
> solution would be to store the input parameters in a map, then take the<br>
> first row of the result set and add the values to that map, replacing any<br>
> existing (INOUT) values. Lastly we would order the parameters according to<br>
> the function prototype, which I am not even sure is unambiguous, because<br>
> proc(IN a, OUT b) and proc(IN a) are equal as prototypes. This is very<br>
> hacky and probably impossible. Moreover it provides no value in PostgreSQL.<br>
> That's why I think a null return value would be best. It best reflects what<br>
> PostgreSQL stored procedures actually return: nothing, directly.<br>
<br>
If I understand correctly the PG C API doesn't support<br>
IN/OUT and OUT parameters, right ?<br>
<br>
If that's the case, I'd simply copy the input parameter<br>
tuple and return this as output - this is what other interfaces<br>
do too when they don't support IN/OUT and OUT parameters.<br>
You already have all the values in Python, so the copy<br>
operation usually is a mere Py_INCREF on the parameters<br>
tuple.<br>
<br>
If PG documents that IN/OUT and OUT parameters are sent<br>
back via result sets, using this approach is what the PG<br>
users expect, so it seems like the most natural way to<br>
do this.<br>
<br>
I'm not sure whether using None as place holder is a good<br>
solution, but then again: if they are not updated by the<br>
driver anyway, this may work as well.<br>
<br>
Cheers,<br>
--<br>
Marc-Andre Lemburg<br>
eGenix.com<br>
<br>
Professional Python Services directly from the Experts (#1, Dec 07 2015)<br>
>>> Python Projects, Coaching and Consulting ... <a href="http://www.egenix.com/" rel="noreferrer" target="_blank">http://www.egenix.com/</a><br>
>>> Python Database Interfaces ... <a href="http://products.egenix.com/" rel="noreferrer" target="_blank">http://products.egenix.com/</a><br>
>>> Plone/Zope Database Interfaces ... <a href="http://zope.egenix.com/" rel="noreferrer" target="_blank">http://zope.egenix.com/</a><br>
________________________________________________________________________<br>
<br>
::: We implement business ideas - efficiently in both time and costs :::<br>
<br>
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48<br>
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg<br>
Registered at Amtsgericht Duesseldorf: HRB 46611<br>
<a href="http://www.egenix.com/company/contact/" rel="noreferrer" target="_blank">http://www.egenix.com/company/contact/</a><br>
<a href="http://www.malemburg.com/" rel="noreferrer" target="_blank">http://www.malemburg.com/</a><br>
<br>
<br>
> Cheers,<br>
> Milosz<br>
><br>
> On Fri, Dec 4, 2015, 16:05 Mike Bayer <<a href="mailto:mike_mp@zzzcomputing.com" target="_blank">mike_mp@zzzcomputing.com</a>> wrote:<br>
><br>
>><br>
>><br>
>> On 12/01/2015 12:40 AM, Milosz Kosmider wrote:<br>
>>> Hi folks,<br>
>>><br>
>>> As PostgreSQL has had named parameters in stored procedures since<br>
>>> version 9.0, I<br>
>>> have taken to adding support for the feature in the DBAPI-complicant<br>
>> Python<br>
>>> wrapper, psycopg2: <a href="https://github.com/psycopg/psycopg2/pull/16/files" rel="noreferrer" target="_blank">https://github.com/psycopg/psycopg2/pull/16/files</a>.<br>
>>><br>
>>> The feature allows the "callproc" method to be invoked, in addition to<br>
>> the<br>
>>> standard, tuple-y way, like this: cur.callproc('some_proc', { some_param:<br>
>>> 'some_value', ... })<br>
>>><br>
>>> While implementing the feature I stumbled upon the DBAPI spec for<br>
>> callproc:<br>
>>> <a href="http://legacy.python.org/dev/peps/pep-0249/#callproc" rel="noreferrer" target="_blank">http://legacy.python.org/dev/peps/pep-0249/#callproc</a>. It states that<br>
>>> "the result<br>
>>> of the call is returned as modified copy of the input sequence. Input<br>
>>> parameters<br>
>>> are left untouched, output and input/output parameters replaced with<br>
>>> possibly<br>
>>> new values." It then goes onto say that "The procedure may also provide<br>
>>> a result<br>
>>> set as output."<br>
>>><br>
>>> I would like to challenge the former requirement, and make the latter<br>
>>> statement a strict requirement.<br>
>>><br>
>>> The former requirement of returning the modified input sequence<br>
>> effectively<br>
>>> forces cur.callproc to examine its result set and copy it over the input<br>
>>> sequence.<br>
>><br>
>> I believe the term "input/output parameters" refers to OUT parameters<br>
>> specifically. If Postgresql's stored procedure system is entirely<br>
>> result-set based, then "output parameters" just wouldn't apply here;<br>
>> you'd only need return the same list of inputs that was passed.<br>
>><br>
>><br>
>>> This is not only redundant, but in fact undefined for result<br>
>>> sets with<br>
>>> more than one row.<br>
>><br>
>> again, this language refers to OUT parameters, which are not result<br>
>> rows, they are parameters that are writable, as in:<br>
>><br>
>><br>
>> <a href="https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:325417134618" rel="noreferrer" target="_blank">https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:325417134618</a><br>
>><br>
>><br>
>>> The requirement's definition is moreover<br>
>>> self-contradictory,<br>
>>> because output parameters are not *in* the input sequence.<br>
>><br>
>> In your case of Postgresql, they're not. In the case of Oracle, MS SQL<br>
>> Server, DB2, and others, they are (or can be). ;)<br>
>><br>
>><br>
>><br>
>><br>
>>> All stored procedures yield result sets.<br>
>><br>
>> that is entirely untrue for many databases.<br>
>><br>
>> SQL Server stored procedures for example usually return an integer<br>
>> return code. They can also use OUT parameters, return multiple result<br>
>> sets, or a detached cursor object:<br>
>><br>
>> <a href="https://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx" rel="noreferrer" target="_blank">https://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx</a><br>
>><br>
>><br>
>><br>
>>> Some queries yield empty result<br>
>>> sets.<br>
>>> Some stored procedures yield empty result sets. As such, I would argue<br>
>> that,<br>
>>> like a query (e.g. cur.execute on a SELECT statement) cur.callproc<br>
>>> *must* endow<br>
>>> the cursor with a result set.<br>
>>><br>
>>> I propose changing the DBAPI spec on callproc to be as follows:<br>
>>><br>
>>> (This method is optional since not all databases provide stored<br>
>>> procedures.)<br>
>>><br>
>>> Call a stored database procedure with the given name. The sequence of<br>
>>> parameters<br>
>>> must contain one entry for each argument that the procedure expects.<br>
>>> Overloaded<br>
>>> procedures are supported. If the underlying database supports named<br>
>>> parameters<br>
>>> in stored procedures, the sequence of parameters may be given as a<br>
>>> dictionary-like object mapping parameter names to values.<br>
>>><br>
>>> The procedure must provide a result set as output. This is then made<br>
>>> available<br>
>>> through the standard fetch* methods.<br>
>>><br>
>>> Return values are not defined.<br>
>>><br>
>>> Thank you,<br>
>>> Milosz<br>
>>><br>
>>><br>
>>> _______________________________________________<br>
>>> DB-SIG maillist - <a href="mailto:DB-SIG@python.org" target="_blank">DB-SIG@python.org</a><br>
>>> <a href="https://mail.python.org/mailman/listinfo/db-sig" rel="noreferrer" target="_blank">https://mail.python.org/mailman/listinfo/db-sig</a><br>
>>><br>
>> _______________________________________________<br>
>> DB-SIG maillist - <a href="mailto:DB-SIG@python.org" target="_blank">DB-SIG@python.org</a><br>
>> <a href="https://mail.python.org/mailman/listinfo/db-sig" rel="noreferrer" target="_blank">https://mail.python.org/mailman/listinfo/db-sig</a><br>
>><br>
><br>
><br>
><br>
> _______________________________________________<br>
> DB-SIG maillist - <a href="mailto:DB-SIG@python.org" target="_blank">DB-SIG@python.org</a><br>
> <a href="https://mail.python.org/mailman/listinfo/db-sig" rel="noreferrer" target="_blank">https://mail.python.org/mailman/listinfo/db-sig</a><br>
><br>
</blockquote></div></div>