Re: Stored procedures and out parameters

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,
because:
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.

During the development of procedures, Peter Eisentraut
posted an experimental patch [1] that demonstrated
#1 and #2 and told he didn't have the time to pursue
this direction, but hopefully that doesn't mean
we'll never be able to do that in PG12 or after.

Functions cannot do #1 or #2, but they can be called
anywhere in a query. Procedures should be able
to do #1 or #2, but they cannot be called
within DML queries.

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.

Personally I understood it as an important conceptual distinction
between functions and procedures that the function exposes a
contract to return a specific datatype that can be unambiguously
determined at PREPARE time, whereas the
procedure is specifically not bound by any such contract,
with the consequence that CALL proc(..) cannot be
prepared, or possibly that it cannot be run through
prepare/bind/execute steps with the extended query protocol,
unlike SELECT or more generally DML queries.

Is that understanding of procedures vs functions incorrect?

[1]
https://www.postgresql.org/message-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite