Thread: Support for OUT parameters in procedures

Procedures currently don't allow OUT parameters.  The reason for this is 
that at the time procedures were added (PG11), some of the details of 
how this should work were unclear and the issue was postponed.  I am now 
intending to resolve this.

AFAICT, OUT parameters in _functions_ are not allowed per the SQL 
standard, so whatever PostgreSQL is doing there at the moment is mostly 
our own invention.  By contrast, I am here intending to make OUT 
parameters in procedures work per SQL standard and be compatible with 
the likes of PL/SQL.

The main difference is that for procedures, OUT parameters are part of 
the signature and need to be specified as part of the call.  This makes 
sense for nested calls in PL/pgSQL like this:

CREATE PROCEDURE test_proc(IN a int, OUT b int)
LANGUAGE plpgsql
AS $$
BEGIN
   b := a * 2;
END;
$$;

DO $$
DECLARE _a int; _b int;
BEGIN
   _a := 10;
   CALL test_proc(_a, _b);
   RAISE NOTICE '_a: %, _b: %', _a, _b;
END
$$;

For a top-level direct call, you can pass whatever you want, since all 
OUT parameters are presented as initially NULL to the procedure code. 
So you could just pass NULL, as in CALL test_proc(5, NULL).

The code changes to make this happen are not as significant as I had 
initially feared.  Most of the patch is expanded documentation and 
additional tests.  In some cases, I changed the terminology from "input 
parameters" to "signature parameters" to make the difference clearer. 
Overall, while this introduces some additional conceptual complexity, 
the way it works is pretty obvious in the end, and people porting from 
other systems will find it working as expected.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> For a top-level direct call, you can pass whatever you want, since all
> OUT parameters are presented as initially NULL to the procedure code.
> So you could just pass NULL, as in CALL test_proc(5, NULL).

Is that actually how other systems work? I would think that people
would expect to pass, say, a package variable, and expect that it will
get updated.

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

On 2020-08-27 15:56, Robert Haas wrote:
> On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> For a top-level direct call, you can pass whatever you want, since all
>> OUT parameters are presented as initially NULL to the procedure code.
>> So you could just pass NULL, as in CALL test_proc(5, NULL).
> 
> Is that actually how other systems work? I would think that people
> would expect to pass, say, a package variable, and expect that it will
> get updated.

The handling of results of SQL statements executed at the top level 
(a.k.a. direct SQL) is implementation-specific and varies widely in 
practice.  More interesting in practice, in terms of functionality and 
also compatibility, are nested calls in PL/pgSQL as well as integration 
in JDBC.

We already support INOUT parameters in procedures, so the method of 
returning the value of output parameters after the CALL already exists. 
This patch doesn't touch that at all, really.  If we had or would add 
other places to put those results, such as package variables, then they 
could be added independently of this patch.

Of course, feedback from those more knowledgeable in other systems than 
me would be welcome.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Aug 28, 2020 at 2:04 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> The handling of results of SQL statements executed at the top level
> (a.k.a. direct SQL) is implementation-specific and varies widely in
> practice.  More interesting in practice, in terms of functionality and
> also compatibility, are nested calls in PL/pgSQL as well as integration
> in JDBC.

I agree that driver integration, and in particular JDBC integration,
is important and needs some thought. I don't think it horribly
matters, with a feature like this, what shows up when people type
stuff into psql. Whatever it is, people will get used to it. But when
they interact through a driver, it's different. It is no good
inventing things, either in PostgreSQL or in the JDBC driver for
PostgreSQL, that make PostgreSQL behave differently from every other
database they use. I don't know exactly how we get to a good outcome
here, but I think it's worth some careful consideration.

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

On 8/27/20 4:34 AM, Peter Eisentraut wrote:
> Procedures currently don't allow OUT parameters.  The reason for this
> is that at the time procedures were added (PG11), some of the details
> of how this should work were unclear and the issue was postponed.  I
> am now intending to resolve this.
>
> AFAICT, OUT parameters in _functions_ are not allowed per the SQL
> standard, so whatever PostgreSQL is doing there at the moment is
> mostly our own invention.  By contrast, I am here intending to make
> OUT parameters in procedures work per SQL standard and be compatible
> with the likes of PL/SQL.
>
> The main difference is that for procedures, OUT parameters are part of
> the signature and need to be specified as part of the call.  This
> makes sense for nested calls in PL/pgSQL like this:
>
> CREATE PROCEDURE test_proc(IN a int, OUT b int)
> LANGUAGE plpgsql
> AS $$
> BEGIN
>   b := a * 2;
> END;
> $$;
>
> DO $$
> DECLARE _a int; _b int;
> BEGIN
>   _a := 10;
>   CALL test_proc(_a, _b);
>   RAISE NOTICE '_a: %, _b: %', _a, _b;
> END
> $$;
>
> For a top-level direct call, you can pass whatever you want, since all
> OUT parameters are presented as initially NULL to the procedure code.
> So you could just pass NULL, as in CALL test_proc(5, NULL).
>
> The code changes to make this happen are not as significant as I had
> initially feared.  Most of the patch is expanded documentation and
> additional tests.  In some cases, I changed the terminology from
> "input parameters" to "signature parameters" to make the difference
> clearer. Overall, while this introduces some additional conceptual
> complexity, the way it works is pretty obvious in the end, and people
> porting from other systems will find it working as expected.
>


I've reviewed this, and I think it's basically fine. I've made an
addition that adds a test module that shows how this can be called from
libpq - that should be helpful (I hope) for driver writers.


A combined patch with the original plus my test suite is attached.


I think this can be marked RFC.


cheers


andrew



-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2020-09-29 08:23, Pavel Stehule wrote:
> This was an important issue if I remember well.  Passing mandatory NULL 
> as OUT arguments solves this issue.
> I fully agree so OUT arguments are part of the procedure's signature. 
> Unfortunately, there is another difference
> from functions, but I don't think so there is a better solution, and we 
> should live with it. I think it can work well.

This has been committed.

> I found one issue. The routine for selecting function or procedure based 
> on signature should be fixed.
> 
> CREATE OR REPLACE PROCEDURE public.procp(OUT integer)
>   LANGUAGE plpgsql
> AS $procedure$
> BEGIN
>    $1 := 10;
> END;
> $procedure$
> 
> DO
> $$
> DECLARE n numeric;
> BEGIN
>    CALL procp(n);
>    RAISE NOTICE '%', n;
> END;
> $$;
> ERROR:  procedure procp(numeric) does not exist
> LINE 1: CALL procp(n)
>               ^
> HINT:  No procedure matches the given name and argument types. You might 
> need to add explicit type casts.
> QUERY:  CALL procp(n)
> CONTEXT:  PL/pgSQL function inline_code_block line 4 at CALL

This is normal; there is no implicit cast from numeric to int.  The same 
error happens if you call a function foo(int) with foo(42::numeric).

> postgres=# create or replace procedure px(anyelement, out anyelement)
> as $$
> begin
>    $2 := $1;
> end;
> $$ language plpgsql;
> 
> postgres=# call px(10, null);
> ERROR:  cannot display a value of type anyelement
> 
> but inside plpgsql it works
> do $$
> declare xx int;
> begin
>    call px(10, xx);
>    raise notice '%', xx;
> end;
> $$;

This might be worth further investigation, but since it happens also 
with INOUT parameters, it seems orthogonal to this patch.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services