Re: Stored procedures and out parameters

On Thu, Aug 30, 2018 at 7:45 PM, Chapman Flack <chap(at)anastigmatix(dot)net> wrote:
> On 08/30/18 15:35, Robert Haas wrote:
>> On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut
>> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>>> CALL compatible with the SQL standard. For example, if you have a
>>> function f1(IN a int, OUT b int), you would call it as SELECT f1(x)
>>> and the "b" would somehow be the return value. But a procedure call
>>> would be CALL p1(x, y), where x and y could be, say, PL/pgSQL
>>> variables.
>
> I suppose the key question for most driver writers is going to be,
> what does that difference look like at the fe-be protocol level?

I don't think the issue is so much the FE-BE protocol level as the SQL syntax.

> PL/pgSQL might be an unrepresentative example for that question,
> as it lives in the backend and could have some other way of retrieving
> b to store in y. For any remote client, the result still needs to get
> back there before the client can apply any "this result gets assigned
> to my y variable" semantics, and is there any material difference between
> the protocol message sequences that return these results
>
> select foo(1,2);
> select * from foo(1,2);
> call bar(1,2);
>
> to the client? And, in the parallel universe where functions got
> implemented according to the standard, what in that picture would
> be different?

You may (or may not) be missing the point here. Your first two
examples do not obviously involve OUT parameters, although in theory
they could, since whatever OUT parameters exist are going to show up
in the third one. The third one definitely does not, since CALL
apparently would require a variable that could be set to be passed as
an argument. I'm not actually sure how it's supposed to work,
actually, because the documentation for CALL addresses neither the
proper usage nor the incompatibility as compared with functions:

https://www.postgresql.org/docs/11/static/sql-call.html

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company