Re: INOUT parameters in procedures

On Wed, Feb 28, 2018 at 4:28 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> This patch set adds support for INOUT parameters to procedures.
> Currently, INOUT and OUT parameters are not supported.
>
> A top-level CALL returns the output parameters as a result row. In
> PL/pgSQL, I have added special support to pass the output back into the
> variables, as one would expect.
>
> These patches apply on top of the "prokind" patch set v2. (Tom has
> submitted an updated version of that, which overlaps with some of the
> changes I've made here. I will work on consolidating that soon.)

I did a pull from master to play around with INOUT parameters and got
some strange interactions with DEFAULT. Specifically, DEFAULT doesn't
do much beyond, 'return the last supplied value given'. I'm not sure
if this is expected behavior; it seems odd:

postgres=# create or replace procedure p(a inout int default 7) as $$
begin return; end; $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p();
a
───

(1 row)

postgres=# call p(3);
a
───
3
(1 row)

postgres=# call p();
a
───
3
(1 row)

I got null,3,3. I would have expected 7,3,7. Default arguments might
remove quite some of the pain associated with having to supply bogus
arguments to get the INOUT parameters working.

Edit: In one case, after dropping the function and recreating it, I
got the procedure to return 0 where it had not before, so this smells
like a bug.
postgres=# call p();
2018-03-20 09:04:50.543 CDT [21494] ERROR: function p() does not
exist at character 6
2018-03-20 09:04:50.543 CDT [21494] HINT: No function matches the
given name and argument types. You might need to add explicit type
casts.
2018-03-20 09:04:50.543 CDT [21494] STATEMENT: call p();
ERROR: function p() does not exist
LINE 1: call p();
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Time: 0.297 ms
postgres=# create or replace procedure p(a inout int default 7) as $$
begin return; end; $$ language plpgsql;
CREATE PROCEDURE
Time: 1.182 ms
postgres=# call p();
a
───
0
(1 row)

merlin