Re: Stored procedures and out parameters

On Tue, Jul 24, 2018 at 11:31 AM, Daniel Verite <daniel@manitou-mail.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.