Support for OUT parameters in procedures

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

po 28. 9. 2020 v 18:43 odesílatel Andrew Dunstan <
andrew.dunstan@2ndquadrant.com> napsal:

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

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.

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

I think this example should work.

But it doesn't work now for INOUT, and this fix will not be easy, so it
should be solved as a separate issue. This features are complete and useful
now, and it can be fixed later without problems with compatibility issues.

Another issue are using polymorphic arguments

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;
$$;

I think this can be marked RFC.

+1

Pavel

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

Just saw this on hackers. Anyon care to comment ?

Dave Cramer
www.postgres.rocks

---------- Forwarded message ---------
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 28 Aug 2020 at 09:31
Subject: Re: Support for OUT parameters in procedures
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>

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

po 5. 10. 2020 v 11:46 odesílatel Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> napsal:

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

this is OUT argument - so direction is reversed - and implicit cast from
int to numeric exists.

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.

yes - this breaks using varchar against text argument, although these types
are almost identical.

Jdbi got a feature request for such parameters a while back:
https://github.com/jdbi/jdbi/issues/1606

The user uses Oracle which I don't really care to install. When I tried to
implement the feature using Postgres,
I found the driver support too lacking to proceed.

So there's some interest out there in making it work, and I can volunteer
to at least smoke test it with my test cases...

On Mon, Oct 5, 2020 at 3:54 AM Dave Cramer <davecramer@postgres.rocks>
wrote:

On Mon, 5 Oct 2020 at 12:17, Steven Schlansker <stevenschlansker@gmail.com>
wrote:

Jdbi got a feature request for such parameters a while back:
https://github.com/jdbi/jdbi/issues/1606

The user uses Oracle which I don't really care to install. When I tried
to implement the feature using Postgres,
I found the driver support too lacking to proceed.

So there's some interest out there in making it work, and I can volunteer
to at least smoke test it with my test cases...

Sure, lets see how broken it is right now.

Dave Cramer
www.postgres.rocks

On 10/5/20 12:24 PM, Dave Cramer wrote:

On Mon, 5 Oct 2020 at 12:17, Steven Schlansker
<stevenschlansker@gmail.com <mailto:stevenschlansker@gmail.com>> wrote:

Jdbi got a feature request for such parameters a while back:
https://github.com/jdbi/jdbi/issues/1606

The user uses Oracle which I don't really care to install.  When I
tried to implement the feature using Postgres,
I found the driver support too lacking to proceed.

So there's some interest out there in making it work, and I can
volunteer to at least smoke test it with my test cases...

Sure, lets see how broken it is right now.

We're working on it. It's a bit tricky, but we need to get it working,
for sure. The main thing is that the driver needs to send some type
other than VOID for the OUT param. Minimally that can be UNKNOWN, but it
should probably reflect the type set in registerOutParameter().

cheers

andrew

--
Andrew Dunstan
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, 5 Oct 2020 at 14:59, Andrew Dunstan <andrew@dunslane.net> wrote:

On 10/5/20 12:24 PM, Dave Cramer wrote:

On Mon, 5 Oct 2020 at 12:17, Steven Schlansker
<stevenschlansker@gmail.com <mailto:stevenschlansker@gmail.com>> wrote:

Jdbi got a feature request for such parameters a while back:
https://github.com/jdbi/jdbi/issues/1606

The user uses Oracle which I don't really care to install. When I
tried to implement the feature using Postgres,
I found the driver support too lacking to proceed.

So there's some interest out there in making it work, and I can
volunteer to at least smoke test it with my test cases...

Sure, lets see how broken it is right now.

We're working on it. It's a bit tricky, but we need to get it working,
for sure. The main thing is that the driver needs to send some type
other than VOID for the OUT param. Minimally that can be UNKNOWN, but it
should probably reflect the type set in registerOutParameter().

I would think we run into the normal issues with things like timestamps

and dates with and without time zones

Thanks,

Dave Cramer
www.postgres.rocks