Re: Stored procedures and out parameters

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

> David G. Johnston wrote:
>
> > > 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.
>
> It does care, because CREATE FUNCTION has a RETURNS clause and
> matching RETURN statements in the body, whereas CREATE PROCEDURE
> doesn't and (will?) have a different syntax for producing resultsets.
>

​But why does whatever code that implements CALL have to care?

In Object Oriented terms why can not both procedures and functions
implement a "EXECUTE_VIA_CALL" interface; while functions additionally
implement a "EXECUTE_VIA_SELECT" interface - the one that they do today.

ISTM that any (most) function could be trivially ​rewritten into a
procedure (or wrapped by one) in a mechanical fashion which could then be
executed via CALL. I'm proposing that instead of having people write their
own wrappers we figure out what the mechanical wrapper looks like, ideally
based upon the public API of the function, and create it on-the-fly
whenever said function is executed via a CALL statement.

As for the invocation, that's just the starting point. At this point
> the driver doesn't know from '{call x}' whether x is a procedure or a
> function in Postgres, hence the request for a syntax that would work
> for both. Okay, but aside from that, if there are results, the driver
> needs to get them in a way that works without knowing wether it's a
> function or procedure. How would that happen?
>

I'm saying that the driver needs to rewrite {call x} as "CALL x()" and
expect optional resultsets and optional output arguments. For functions
invoked as procedures this would be a single resultset with zero output
arguments. Which is exactly the same end-user result that is received
today when "SELECT * FROM x()" is used.

> Back to the first message of the thread, Shay Rojansky was saying:
>
> "However, connecting via Npgsql, which uses the extended protocol, I
> see something quite different. As a response to a Describe PostgreSQL
> message, I get back a NoData response rather than a RowDescription
> message"
>
> Why would a Describe on a "CALL myproc()" even work if we
> accept the premise that myproc() does not advertise what it may return,
> contrary to a "SELECT * FROM function()"?
> This issue goes beyond a "syntactic bridge" between CALL and SELECT.

​If you "DESCRIBE CALL my_func()" you get back a NoData response; it
doesn't try to inspect the RETURNS clause of the function even though in
theory it could. The client is using CALL so that is it should expect to
receive. That said I'm not entirely clear whether the NoData response is a
fixed bug or not...

David J.