Re: Stored procedures and out parameters

Peter, Tom,

Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with the
current PostgreSQL protocol are unclear to me as a client driver
maintainer... Some clarification would really help.

Also another request by Vladimir and myself to consider allowing functions
to be invoked with CALL, in order to provide a single way to call both
procedures and functions - this is important as language database APIs
typically have a single, database-independent way to invoke server-side
code that does not distinguish between functions and procedures.

Thanks for your time!

> 1. A stored procedure should be able to return multiple resultsets with
> different structures.
> > 2. A stored procedure can decide dynamically of the structure of the
> resultset(s) it returns, and the caller will discover it as they're
> returned, not before.
>
> Both of the above seem to be simply incompatible with the current
> PostgreSQL protocol. Describe currently returns a single RowDescription,
> which describes a single resultset, not more. And as I wrote before, I
> don't see how it's possible with the current protocol for the caller to
> discover the structure of the resultset(s) "as they're returned" - type
> information simply isn't included in the responses to Execute, only field
> lengths and values. It also leads me to wonder what exactly is returned in
> the current implementation when Describe is send on a stored procedure
> call: something *is* returned as Vladimir wrote, meaning that stored
> procedures aren't as dynamic as they're made out to be?
>
> To summarize, it seems to me that if the multiple resultsets and/or
> dynamic resultset structure are a real feature of stored procedure,
> attention must be given to possible impact on the protocol and especially
> how client-side drivers are supposed to interact with the resultsets.
>
> The missing part is "invoke functions via CALL statement".
>>
>
> I agree. This is definitely not a JDBC-specific issue - I'm guessing most
> database APIs out there have their (single) way to invoke server-side code,
> and that way is currently set to send SELECT because only functions existed
> before. The distinction between stored functions and stored procedures
> seems to be PostgreSQL-specific, and the different invocation syntax causes
> a mismatch. Hope you consider allowing invoking the new stored procedures
> with CALL.
>