Re: Stored procedures and out parameters

> Well, no, actually I think it wouldn't. Multiple rowsets coming back
> from a single query is, to my mind anyway, forbidden in the extended query
> mode. Yeah, we could probably get away with it in simple query mode
> (PQexec), but it's very likely to break clients in extended mode, because
> they're going to be expecting just a single PGresult from a single SQL
> command. Moreover, there are aspects of the protocol, such as the
> Describe command, that aren't capable of dealing with more than one
> result row descriptor per query. It would take some investigation to
> determine the consequences of changing that. Even if you can weasel-word
> your way into claiming that it's not a complete protocol break, I for one
> would not vote to allow it unless the client has specifically said it
> could handle it.
>
> The protocol extension features we recently put in could be used to tell
> whether libpq or equivalent wire-level driver allows the case, but I'm
> just as concerned about breaking application-layer logic above the driver,
> and it's pretty unclear how we ought to deal with telling whether that
> code is OK with this.
>
> As long as we're sure that the case is prevented in v11, it's something
> that we can leave to work on later.
>

Just to say that from the perspective of a driver writer, this is
absolutely true. The protocol docs explicitly say that the response to
Describe is "a RowDescription message describing the rows that will be
returned by executing the portal", and any deviation from this will likely
cause significant breakage client-side. So a protocol version change is
necessary in my opinion for this.

By the way, from a purely protocol point of view, if you allow stored
procedures to return multiple resultsets, you may as well consider allowing
regular statements to contain semicolons and return multiple resultsets as
well - just like the simple protocol... This obviously would have
consequence beyond a pure protocol change, but it would make thinks more
consistent and would also simplify certain client-side implementation
details.

>> 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.
>
> > I am familiar with the Java {call} escape. But I think it's pretty
> > useless.
>

It would be good to understand why you think it's useless (am not familiar
at all with JDBC, am genuinely interested). On the .NET side it's a pretty
common/simple API (CommandType.StoredProcedure) that most users expect
coming from other databases, hence important for portability and user
acquisition.

I'd also be -1 on enabling this without a lot more thought. It might
> be fine to allow it, but if it turns out it's not fine, we'd have painted
> ourselves into a corner. I don't think that late in the release cycle
> is the time to be making such decisions.
>

I realize this is late and it's obviously not a quick and easy decision. On
the other hand, releasing *without* this also has its consequence, namely
setting in stone that the database-independent language API cannot be used
for invoking the new stored procedures. Even if you decide to add this for
PostgreSQL 12, users will have already written code that will need to
execute against PostgreSQL 11, and will therefore have to avoid the
database-independent API altogether and construct CALL statements
themselves.

So I hope you at least consider going through the thought process about
allowing this.