Re: INOUT parameters in procedures

2018-03-20 15:05 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

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

I wrote patch

Regards

Pavel

>
>
> merlin
>
>