Re: Stored procedures and out parameters

On 09/02/18 16:37, Robert Haas wrote:
> On Thu, Aug 30, 2018 at 7:45 PM, Chapman Flack <chap(at)anastigmatix(dot)net> wrote:

>> 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);
>
> 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,

A fair point, as I didn't include the declarations in the email.
They NON-obviously involve OUT parameters, or rather INOUT ones.
In 11beta3 you can't give a procedure OUT parameters:

# show server_version;
server_version
----------------
11beta3

# create procedure bar(IN a int, OUT b int) as 'select $1' language sql;
ERROR: procedures cannot have OUT arguments
HINT: INOUT arguments are permitted.

So I went with INOUT for the second param of both the procedure bar and
the function foo (even though a pure OUT parameter is accepted for foo).

# create procedure bar(IN a int, INOUT b int) as 'select 9*$1' language sql;
CREATE PROCEDURE
# create function foo(IN a int, INOUT b int) as 'select 9*$1' language sql;
CREATE FUNCTION

That requires passing something for b in the calls, though it isn't used:

# select foo(1,2); select * from foo(1,2); call bar(1,2);
foo
-----
9
(1 row)

b
---
9
(1 row)

b
---
9

Aside from the different column label in select foo vs select * from foo,
there seems to be little difference in how the result set gets back to
the client (I haven't snooped the protocol exchanges, though).

I understand that (part of) the issue is a common syntax that {call foo...}
should expand into to make the Right Thing happen, but I was trying to
take one step back and gauge how clear it is what the Right Thing should be.

-Chap