Re: Stored procedures and out parameters

On Tue, Jul 24, 2018 at 6:58 AM, Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> Vladimir Sitnikov wrote:
>
> > There's no notion if the called object is a procedure or function.
> > Note: PostgreSQL can have a function that `returns void`, and it is hard
> to
> > tell if {call test()} refers to a function or procedure.
> >
> > Can functions and procedures be unified at the backend level?
> > For instance, support "CALL" for both of them.
> > Or support "select * ..." for both of them.
>
> But there's a reason why CALL exists in the first place.
> It's not a synonym of SELECT and not supposed to do the
> same thing.
> In a SELECT or in a DML query in general you must
> be able to determine the structure of the resultset
> without executing the query.
>
> In a CALL you're not supposed to be able to do that,
>

​I disagree: "In a CALL you're are not required to determine the resultset
structure prior to execution"

> because:
> 1. A stored procedure should be able to return multiple
> resultsets with different structures.
>

​But it may only return one

> 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.
>

The function itself doesn't care - this concern is about SELECT vs CALL
invocation only, not the script definition.

> Procedures should be able
> to do #1 or #2, but they cannot be called
> within DML queries.
>

​Immaterial, we are talking about a straight "CALL proc()" statement here.
​

> I fear like what is being asked here, to blur the distinction
> between functions and procedures in terms how the
> client-side workflow expects and handle results,
> would mean that we're going to loose the ability to
> do #1 and #2 in the future.
>

​I don't see how allowing a function to be used as the object of CALL, but
enforcing the existing CALL dynamics, will lead to that situation.

What I don't know is whether the limitations that are being imposed for
CALL will break JDBC if existing SELECT statements are changed to CALL
statements. Since JDBC has to be able to deal with CALL statements
manually issued anyway it should be fairly straight forward for someone
knowledgeable with the JDBC codebase to make that determination.

So, while its not really incumbent upon PostgreSQL to compensate for the
decisions made by the JDBC driver PostgreSQL does bear some responsibility
for the current situation due to its long period of non-implementation of
the SQL Standard CALL (and stored procedure) feature. Loosening up the
blanket restriction on functions not being a valid target of a CALL seems
like something that should be strongly considered. Runtime failures for
unsupported situations can still be thrown but to the extent that functions
are effectively a subset of stored procedures it seems like most uses of a
function as a target should be fully compatible with CALL semantics.

David J.