Re: INOUT parameters in procedures

[Merlin Moncure]

On Tue, Mar 20, 2018 at 4:19 PM, Merlin Moncure  wrote:
> A) you can't assign output variables with into:
> CALL p(1) INTO i;  // gives syntax error
>
> B) you can't assign via assignment
> i := p(1); // gives error, 'use CALL'
>
> C) but you *can* via execute
> EXECUTE 'CALL p(1)' INTO i;  // this works!
>
> ...I'm glad 'C' works, as without that there would be no useful way to
> get values out of procedures called from within other
> procedures/functions as things stand today.  'A' ideally also out to
> work, but I'm not sure  'B' should be expected to work since it's
> really a thin layer around SELECT.   What do you think?

Also (sorry for spam),
A procedure created via:
create procedure p() as $$begin call p(); end; $$ language plpgsql;
...will segfault when called -- there ought to be a stack depth check.

merlin

Re: INOUT parameters in procedures

[Merlin Moncure]

On Tue, Mar 20, 2018 at 10:09 AM, Pavel Stehule  wrote:
> 2018-03-20 15:18 GMT+01:00 Merlin Moncure :
>> >> 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
>>
>> Confirmed this fixes the issue.
>
> Thanks for info

You're welcome.  Working with this feature some more, I noticed that:
A) you can't assign output variables with into:
CALL p(1) INTO i;  // gives syntax error

B) you can't assign via assignment
i := p(1); // gives error, 'use CALL'

C) but you *can* via execute
EXECUTE 'CALL p(1)' INTO i;  // this works!

...I'm glad 'C' works, as without that there would be no useful way to
get values out of procedures called from within other
procedures/functions as things stand today.  'A' ideally also out to
work, but I'm not sure  'B' should be expected to work since it's
really a thin layer around SELECT.   What do you think?

merlin

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-20 15:18 GMT+01:00 Merlin Moncure :

> On Tue, Mar 20, 2018 at 9:09 AM, Pavel Stehule 
> wrote:
> >> 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
>
> Confirmed this fixes the issue.
>

Thanks for info

Pavel


>
> merlin
>

Re: INOUT parameters in procedures

[Merlin Moncure]

On Tue, Mar 20, 2018 at 9:09 AM, Pavel Stehule  wrote:
>> 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

Confirmed this fixes the issue.

merlin

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-20 15:05 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>:

> On Wed, Feb 28, 2018 at 4:28 PM, Peter Eisentraut
> <peter.eisentr...@2ndquadrant.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
>
>
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 86fa8c0dd7..c7a44d858b 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -55,6 +55,7 @@
 #include "executor/executor.h"
 #include "miscadmin.h"
 #include "optimizer/var.h"
+#include "optimizer/clauses.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_collate.h"
 #include "parser/parse_expr.h"
@@ -2254,6 +2255,9 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 		elog(ERROR, "cache lookup failed for function %u", fexpr->funcid);
 	if (!heap_attisnull(tp, Anum_pg_proc_proconfig))
 		callcontext->atomic = true;
+
+	fexpr->args = expand_function_arguments(fexpr->args, fexpr->funcresulttype, tp);
+
 	ReleaseSysCache(tp);
 
 	/* Initialize function call structure */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a9a09afd2b..40eae3a835 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -130,8 +130,6 @@ static Expr *simplify_function(Oid funcid,
   Oid result_collid, Oid input_collid, List **args_p,
   bool funcvariadic, bool process_args, bool allow_non_const,
   eval_const_expressions_context *context);
-static List *expand_function_arguments(List *args, Oid result_type,
-		  HeapTuple func_tuple);
 static List *reorder_function_arguments(List *args, HeapTuple func_tuple);
 static List *add_function_defaults(List *args, HeapTuple func_tuple);
 static List *fetch_function_defaults(HeapTuple func_tuple);
@@ -4112,7 +4110,7 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
  * cases it handles should never occur there.  This should be OK since it
  * will fall through very quickly if there's nothing to do.
  */
-static List *
+List *
 expand_function_arguments(List *args, Oid result_type, HeapTuple func_tuple)
 {
 	Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple);
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index ba4fa4b68b..ed854fdd40 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -14,9 +14,9 @@
 #ifndef CLAUSES_H
 #define

Re: INOUT parameters in procedures

[Merlin Moncure]

On Wed, Feb 28, 2018 at 4:28 PM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.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

Re: INOUT parameters in procedures

[Rushabh Lathia]

On Tue, Mar 20, 2018 at 6:38 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 3/19/18 03:25, Rushabh Lathia wrote:
> > For the FUNCTION when we have single OUT/INOUT parameter
> > the return type for that function will be set to the type of OUT
> parameter.
> > But in case of PROCEDURE, it's always RECORDOID, why this inconsistency?
>
> For procedures, this is just an implementation detail.  The CALL command
> returns a row in any case, so if we set the return type to a scalar
> type, we'd have to add special code to reassemble a row anyway.  For
> functions, the inconsistency is (arguably) worth it, because it affects
> how functions can be written and called, but for procedures, there would
> be no point.
>
>
This feel like inconsistency with the existing system object FUNCTION.
It would be nice to be consistent with the FUNCTION - which set the
prorettype as the type of single IN/OUT in case of single argument.

If CALL command returns a row in any case, then I think adding logic
to build row while building the output for CALL statement make more sense.


> > Above test throws an error saying calling procedures with output
> > arguments are not supported in SQL functions.  Whereas similar test
> > do work with SQL functions:
>
> This was discussed earlier in the thread.
>
> The behavior of output parameters in functions was, AFAICT, invented by
> us.  But for procedures, the SQL standard specifies it, so there might
> be some differences.
>
>
Sorry, but I am still unable to understand the difference.
In case of PROCEDURE, it's calling the PROCEDURE with out parameter.
So if that we call the same PROCEURE in the psql prompt:

postgres@101361=#CALL ptest4a(null, null);
 a | b
---+---
 1 | 2
(1 row)

and same is the case if we call the FUNCTION in the psql prompt:

postgres@101361=#SELECT * from ftest4b(null, null);
 b | a
---+---
 1 | 2
(1 row)

So if I understand correctly, in the testcase where it's calling the CALL
within SQL procedure - has to throw similar output. Isn't it?


> ERROR:  calling procedures with output arguments is not supported in SQL
> > functions
> > CONTEXT:  SQL function "ptest4b"
> >
> > Here error message says that calling procedures with output arguments is
> not
> > supported in SQL functions.  Whereas here it's getting called from the
> SQL
> > procedure.  So error message needs to be changed.
>
> Well, I don't think we are going to change every single error message
> from "function" to a separate function and procedure variant.
>
>
I think we should, otherwise it pass the wrong message to the user. Like
here it says "calling procedures with output arguments is not supported in
SQL functions"
but actually test is calling the procedures from procedure.  I think now
that
we have a way to ideintify FUNCTION/PROCEDURE (prokind) it's good
to give proper error message.

Recently commit 2c6f37ed62114bd5a092c20fe721bd11b3bcb91e and
8b9e9644dc6a9bd4b7a97950e6212f63880cf18b replace AclObjectKind and
GrantObjectType with ObjectType and with that we now getting proper
object type for the acl error message. In case of PROCEDURE
and FUNCTIONS also error message should send clear message.


Regards,
Rushabh Lathia
www.EnterpriseDB.com

Re: INOUT parameters in procedures

[Peter Eisentraut]

On 3/19/18 03:25, Rushabh Lathia wrote:
> For the FUNCTION when we have single OUT/INOUT parameter 
> the return type for that function will be set to the type of OUT parameter.
> But in case of PROCEDURE, it's always RECORDOID, why this inconsistency?

For procedures, this is just an implementation detail.  The CALL command
returns a row in any case, so if we set the return type to a scalar
type, we'd have to add special code to reassemble a row anyway.  For
functions, the inconsistency is (arguably) worth it, because it affects
how functions can be written and called, but for procedures, there would
be no point.

> Above test throws an error saying calling procedures with output
> arguments are not supported in SQL functions.  Whereas similar test
> do work with SQL functions:

This was discussed earlier in the thread.

The behavior of output parameters in functions was, AFAICT, invented by
us.  But for procedures, the SQL standard specifies it, so there might
be some differences.

> ERROR:  calling procedures with output arguments is not supported in SQL
> functions
> CONTEXT:  SQL function "ptest4b"
> 
> Here error message says that calling procedures with output arguments is not
> supported in SQL functions.  Whereas here it's getting called from the SQL
> procedure.  So error message needs to be changed. 

Well, I don't think we are going to change every single error message
from "function" to a separate function and procedure variant.

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

Re: INOUT parameters in procedures

[Rushabh Lathia]

Thanks Peter for working on this.  Sorry for the delay in raising this
questions.

1)

@@ -302,7 +304,9 @@ interpret_function_parameter_list(ParseState *pstate,
/* handle output parameters */
if (fp->mode != FUNC_PARAM_IN && fp->mode != FUNC_PARAM_VARIADIC)
{
-   if (outCount == 0)  /* save first output param's type */
+   if (objtype == OBJECT_PROCEDURE)
+   *requiredResultType = RECORDOID;
+   else if (outCount == 0) /* save first output param's type */
*requiredResultType = toid;
outCount++;

For the FUNCTION when we have single OUT/INOUT parameter
the return type for that function will be set to the type of OUT parameter.
But in case of PROCEDURE, it's always RECORDOID, why this inconsistency?

postgres@39755=#select proname, prorettype from pg_proc where proname =
'foo';
 proname | prorettype
-+
 foo | 23
(1 row)


postgres@39755=#CREATE PROCEDURE foo_pro(INOUT a int)
LANGUAGE plpgsql
AS $$
begin
SELECT 1 into a;
end;$$;
CREATE PROCEDURE
postgres@39755=#select proname, prorettype from pg_proc where proname =
'foo_pro';
 proname | prorettype
-+
 foo_pro |   2249
(1 row)

2) Inconsistency in procedure behavior - compared to function.

drop procedure ptest4a;
drop procedure ptest4b;
CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int)
LANGUAGE plpgsql
AS $$
begin
SELECT 1, 2 into a, b;
end;$$;

CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b);
$$;
ERROR:  calling procedures with output arguments is not supported in SQL
functions
CONTEXT:  SQL function "ptest4b"

Above test throws an error saying calling procedures with output
arguments are not supported in SQL functions.  Whereas similar test
do work with SQL functions:

CREATE FUNCTION ftest4a(INOUT a int, INOUT b int) returns record
LANGUAGE plpgsql
AS $$
begin
SELECT 1, 2 into a, b;
end;$$;

CREATE FUNCTION ftest4b(INOUT b int, INOUT a int) returns record
LANGUAGE SQL
AS $$
SELECT ftest4a(a, b);
$$;

postgres@39755=#SELECT ftest4b(null, null);
 ftest4b
-
 (1,2)
(1 row)


3)

CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b);
$$;
ERROR:  calling procedures with output arguments is not supported in SQL
functions
CONTEXT:  SQL function "ptest4b"

Here error message says that calling procedures with output arguments is not
supported in SQL functions.  Whereas here it's getting called from the SQL
procedure.  So error message needs to be changed.


Thanks,
Rushabh Lathia
www.EnterpriseDB.com

Re: INOUT parameters in procedures

[Tom Lane]

Jeff Janes  writes:
> On Thu, Mar 15, 2018 at 6:58 AM, Tom Lane  wrote:
>> Not fixed by 8df5a1c868cc28f89ac6221cff8e2b5c952d0eb6?

> I think you meant to type "now fixed by".  (unless your compiler is pickier
> than mine)

Actually what I meant was "doesn't that commit fix it for you?"

regards, tom lane

Re: INOUT parameters in procedures

[Jeff Janes]

On Thu, Mar 15, 2018 at 6:58 AM, Tom Lane  wrote:

> Jeff Janes  writes:
> > I'm getting compiler warnings:
> > pl_exec.c: In function 'exec_stmt_call':
> > pl_exec.c:2089:8: warning: variable 'numargs' set but not used
>
> Not fixed by 8df5a1c868cc28f89ac6221cff8e2b5c952d0eb6?
>

I think you meant to type "now fixed by".  (unless your compiler is pickier
than mine)

Cheers

Jeff

Re: INOUT parameters in procedures

[Tom Lane]

Jeff Janes  writes:
> I'm getting compiler warnings:
> pl_exec.c: In function 'exec_stmt_call':
> pl_exec.c:2089:8: warning: variable 'numargs' set but not used

Not fixed by 8df5a1c868cc28f89ac6221cff8e2b5c952d0eb6?

regards, tom lane

Re: INOUT parameters in procedures

[Jeff Janes]

On Wed, Mar 14, 2018 at 10:46 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> committed
>
>
I'm getting compiler warnings:

pl_exec.c: In function 'exec_stmt_call':
pl_exec.c:2089:8: warning: variable 'numargs' set but not used
[-Wunused-but-set-variable]
int   numargs;
^

select version();
PostgreSQL 11devel-6b960aa on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

Cheers,

Jeff

Re: INOUT parameters in procedures

[Peter Eisentraut]

committed

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

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-13 14:14 GMT+01:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

> On 3/8/18 02:25, Pavel Stehule wrote:
> > It looks like some error in this concept. The rules for enabling
> > overwriting procedures should modified, so this collision should not be
> > done.
> >
> > When I using procedure from PL/pgSQL, then it is clear, so I place on
> > *OUT position variables. But when I call procedure from top, then I'll
> > pass fake parameters to get some result.
>
> What we'll probably want to do here is to make the OUT parameters part
> of the identity signature of procedures, unlike in functions.  This
> should be a straightforward change, but it will require some legwork in
> many parts of the code.
>

yes


>
> >if (argmodes && (argmodes[i] == PROARGMODE_INOUT ||
> > argmodes[i] == PROARGMODE_OUT))
> > +   {
> > +   Param  *param;
> >
> > Because PROARGMODE_OUT are disallowed, then this check is little bit
> > messy. Please, add some comment.
>
> Fixed.
>
> I discovered another issue, in LANGUAGE SQL procedures.  Currently, if
> you make a CALL with an INOUT parameter in an SQL procedure, the output
> is thrown away (unless it's the last command).  I would like to keep
> open the option of assigning the results by name, like we do in
> PL/pgSQL.  So in this patch I have made a change to prohibit calling
> procedures with INOUT parameters in LANGUAGE SQL routines (see
> check_sql_fn_statements()).  What do you think?
>

The disabling it, it is probably the best what is possible now. The
variables in SQL are more named parameters than variables. Is not necessary
to complicate it.

Regards

Pavel



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

Re: INOUT parameters in procedures

[Peter Eisentraut]

On 3/8/18 02:25, Pavel Stehule wrote:
> It looks like some error in this concept. The rules for enabling
> overwriting procedures should modified, so this collision should not be
> done.
> 
> When I using procedure from PL/pgSQL, then it is clear, so I place on
> *OUT position variables. But when I call procedure from top, then I'll
> pass fake parameters to get some result.

What we'll probably want to do here is to make the OUT parameters part
of the identity signature of procedures, unlike in functions.  This
should be a straightforward change, but it will require some legwork in
many parts of the code.

>    if (argmodes && (argmodes[i] == PROARGMODE_INOUT ||
> argmodes[i] == PROARGMODE_OUT))
> +   {
> +   Param  *param;
> 
> Because PROARGMODE_OUT are disallowed, then this check is little bit
> messy. Please, add some comment.

Fixed.

I discovered another issue, in LANGUAGE SQL procedures.  Currently, if
you make a CALL with an INOUT parameter in an SQL procedure, the output
is thrown away (unless it's the last command).  I would like to keep
open the option of assigning the results by name, like we do in
PL/pgSQL.  So in this patch I have made a change to prohibit calling
procedures with INOUT parameters in LANGUAGE SQL routines (see
check_sql_fn_statements()).  What do you think?

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 5b9f1506e73826f4f6ff567e54b12c4e232a4263 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 12 Mar 2018 21:39:26 -0400
Subject: [PATCH v4] Support INOUT parameters in procedures

In a top-level CALL, the values of INOUT parameters will be returned as
a result row.  In PL/pgSQL, the values are assigned back to the input
parameters.  In other languages, the same convention as for return a
record from a function is used.  That does not require any code changes
in the PL implementations.

Reviewed-by: Pavel Stehule <pavel.steh...@gmail.com>
---
 doc/src/sgml/plperl.sgml   |  14 +++
 doc/src/sgml/plpgsql.sgml  |  16 +++
 doc/src/sgml/plpython.sgml |  11 ++
 doc/src/sgml/pltcl.sgml|  12 ++
 doc/src/sgml/ref/create_procedure.sgml |   7 +-
 src/backend/catalog/pg_proc.c  |   4 +-
 src/backend/commands/functioncmds.c|  51 +++--
 src/backend/executor/functions.c   |  51 +
 src/backend/tcop/utility.c |   3 +-
 src/backend/utils/fmgr/funcapi.c   |  11 +-
 src/include/commands/defrem.h  |   3 +-
 src/include/executor/functions.h   |   2 +
 src/include/funcapi.h  |   3 +-
 src/pl/plperl/expected/plperl_call.out |  25 +
 src/pl/plperl/sql/plperl_call.sql  |  22 
 src/pl/plpgsql/src/expected/plpgsql_call.out   |  89 +++
 .../plpgsql/src/expected/plpgsql_transaction.out   |   2 +-
 src/pl/plpgsql/src/pl_comp.c   |  10 +-
 src/pl/plpgsql/src/pl_exec.c   | 125 -
 src/pl/plpgsql/src/pl_funcs.c  |  25 +
 src/pl/plpgsql/src/pl_gram.y   |  38 +--
 src/pl/plpgsql/src/pl_scanner.c|   1 +
 src/pl/plpgsql/src/plpgsql.h   |  12 ++
 src/pl/plpgsql/src/sql/plpgsql_call.sql| 108 ++
 src/pl/plpython/expected/plpython_call.out |  23 
 src/pl/plpython/plpy_exec.c|  24 ++--
 src/pl/plpython/sql/plpython_call.sql  |  20 
 src/pl/tcl/expected/pltcl_call.out |  26 +
 src/pl/tcl/sql/pltcl_call.sql  |  23 
 src/test/regress/expected/create_procedure.out |  21 
 src/test/regress/sql/create_procedure.sql  |  19 
 31 files changed, 752 insertions(+), 49 deletions(-)

diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index cff7a847de..9295c03db9 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -278,6 +278,20 @@ PL/Perl Functions and Arguments
hash will be returned as null values.
   
 
+  
+   Similarly, output parameters of procedures can be returned as a hash
+   reference:
+
+
+CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
+my ($a, $b) = @_;
+return {a = $a * 3, b = $b * 3};
+$$ LANGUAGE plperl;
+
+CALL perl_triple(5, 10);
+
+  
+
   
 PL/Perl functions can also return sets of either scalar or
 composite types.  Usually you'll want to return rows one at a
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c1e3c6a19d..6c25116538 100644
--- a/doc/src/sgml/plpgsql.sgml
+++

Re: INOUT parameters in procedures

[Pavel Stehule]

Hi

2018-03-08 1:53 GMT+01:00 Peter Eisentraut :

> On 3/6/18 04:22, Pavel Stehule wrote:
> > why just OUT variables are disallowed?
> >
> > The oracle initializes these values to NULL - we can do same?
>
> The problem is function call resolution.  If we see a call like
>
> CALL foo(a, b, c);
>
> the this could be foo() with zero input and three output parameters, or
> with one input parameter and two output parameters, etc.  We have no
> code to deal with that right now.
>

It looks like some error in this concept. The rules for enabling
overwriting procedures should modified, so this collision should not be
done.

When I using procedure from PL/pgSQL, then it is clear, so I place on *OUT
position variables. But when I call procedure from top, then I'll pass fake
parameters to get some result.

CREATE OR REPLACE PROCEDURE proc(IN a, OUT x, OUT y)
AS $$
BEGIN
  x := a * 10;
  y := a + 10;
END;
$$ LANGUAGE plpgsql;

CALL proc(10) -- has sense

but because just OUT variables are not possible, then the definition must
be changed to CREATE OR REPLACE PROCEDURE proc(IN a, INOUT x, INOUT y)

and CALL proc(10, NULL, NULL) -- looks little bit scarry

I understand so this is not easy solution (and it can be topic for other
releases), but I am thinking so it is solvable - but needs deeper change in
part, where is a routine is selected on signature. Now, this algorithm
doesn't calculate with OUT params.

This enhancing can be interesting for some purposes (and again it can helps
with migration from Oracle - although these techniques are usually used
inside system libraries):

a) taking more info from proc when it is required

PROCEDURE foo(a int);
PROCEDURE foo(a int, OUT detail text)

b) possible to directly specify expected result type

PROCEDURE from_json(a json, OUT int);
PROCEDURE from_json(a json, OUT date);
PROCEDURE from_json(a json, OUT text);

It is clear, so in environments when variables are not available, these
procedures cannot be called doe possible ambiguity.

This point can be closed now, I accept technical limits.




>
> > Minimally this message is not too friendly, there should be hint - "only
> > INOUT is suported" - but better support OUT too - from TOP OUT variables
> > should not be passed. from PL should be required.
>
> Added a hint.
>

ok


>
> > I wrote recursive procedure. The call finished by exception. Why?
>
> Fixed. (memory context issue)
>

tested, it is ok now


>
> I added your example as a test case.
>
> > This issue can be detected in compile time, maybe?
> >
> > postgres=# create or replace procedure p(x int,inout a int, inout b
> numeric)
> > as $$
> > begin raise notice 'xxx % %', a, b;if (x > 1) then
> >   a := x / 10;
> >   b := x / 2; call p(b::int, a, 10); <--- can be detected in compile
> time?
> > end if;
> > end;
> > $$ language plpgsql;
>
> Function resolution doesn't happen at compile time.  That would require
> significant work in PL/pgSQL (possible perhaps, but major work).  Right
> now, we do parse analysis at first execution.
>

ok, understand

looks well

all test passed,
code is well commented,
there are tests

   if (argmodes && (argmodes[i] == PROARGMODE_INOUT ||
argmodes[i] == PROARGMODE_OUT))
+   {
+   Param  *param;

Because PROARGMODE_OUT are disallowed, then this check is little bit messy.
Please, add some comment.

Regards

Pavel




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

Re: INOUT parameters in procedures

[Peter Eisentraut]

On 3/6/18 04:22, Pavel Stehule wrote:
> why just OUT variables are disallowed?
> 
> The oracle initializes these values to NULL - we can do same?

The problem is function call resolution.  If we see a call like

CALL foo(a, b, c);

the this could be foo() with zero input and three output parameters, or
with one input parameter and two output parameters, etc.  We have no
code to deal with that right now.

> Minimally this message is not too friendly, there should be hint - "only
> INOUT is suported" - but better support OUT too - from TOP OUT variables
> should not be passed. from PL should be required.

Added a hint.

> I wrote recursive procedure. The call finished by exception. Why?

Fixed. (memory context issue)

I added your example as a test case.

> This issue can be detected in compile time, maybe?
> 
> postgres=# create or replace procedure p(x int,inout a int, inout b numeric)
> as $$
> begin raise notice 'xxx % %', a, b;if (x > 1) then
>   a := x / 10;
>   b := x / 2; call p(b::int, a, 10); <--- can be detected in compile time?
> end if;
> end;
> $$ language plpgsql;

Function resolution doesn't happen at compile time.  That would require
significant work in PL/pgSQL (possible perhaps, but major work).  Right
now, we do parse analysis at first execution.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 3c5ed2faab30dfcde34dfd58877e45a7f6477237 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 7 Mar 2018 19:15:35 -0500
Subject: [PATCH v3] Support INOUT parameters in procedures

In a top-level CALL, the values of INOUT parameters will be returned as
a result row.  In PL/pgSQL, the values are assigned back to the input
parameters.  In other languages, the same convention as for return a
record from a function is used.  That does not require any code changes
in the PL implementations.
---
 doc/src/sgml/plperl.sgml   |  14 +++
 doc/src/sgml/plpgsql.sgml  |  16 +++
 doc/src/sgml/plpython.sgml |  11 ++
 doc/src/sgml/pltcl.sgml|  12 ++
 doc/src/sgml/ref/create_procedure.sgml |   5 +-
 src/backend/catalog/pg_proc.c  |   3 +-
 src/backend/commands/functioncmds.c|  51 +++--
 src/backend/tcop/utility.c |   3 +-
 src/backend/utils/fmgr/funcapi.c   |  11 +-
 src/include/commands/defrem.h  |   3 +-
 src/include/funcapi.h  |   3 +-
 src/pl/plperl/expected/plperl_call.out |  25 +
 src/pl/plperl/sql/plperl_call.sql  |  22 
 src/pl/plpgsql/src/expected/plpgsql_call.out   |  89 +++
 .../plpgsql/src/expected/plpgsql_transaction.out   |   2 +-
 src/pl/plpgsql/src/pl_comp.c   |  10 +-
 src/pl/plpgsql/src/pl_exec.c   | 125 -
 src/pl/plpgsql/src/pl_funcs.c  |  25 +
 src/pl/plpgsql/src/pl_gram.y   |  38 +--
 src/pl/plpgsql/src/pl_scanner.c|   1 +
 src/pl/plpgsql/src/plpgsql.h   |  12 ++
 src/pl/plpgsql/src/sql/plpgsql_call.sql|  83 ++
 src/pl/plpython/expected/plpython_call.out |  23 
 src/pl/plpython/plpy_exec.c|  24 ++--
 src/pl/plpython/sql/plpython_call.sql  |  20 
 src/pl/tcl/expected/pltcl_call.out |  26 +
 src/pl/tcl/sql/pltcl_call.sql  |  23 
 src/test/regress/expected/create_procedure.out |   1 +
 28 files changed, 632 insertions(+), 49 deletions(-)

diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index cff7a847de..9295c03db9 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -278,6 +278,20 @@ PL/Perl Functions and Arguments
hash will be returned as null values.
   
 
+  
+   Similarly, output parameters of procedures can be returned as a hash
+   reference:
+
+
+CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
+my ($a, $b) = @_;
+return {a = $a * 3, b = $b * 3};
+$$ LANGUAGE plperl;
+
+CALL perl_triple(5, 10);
+
+  
+
   
 PL/Perl functions can also return sets of either scalar or
 composite types.  Usually you'll want to return rows one at a
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c1e3c6a19d..6c25116538 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1870,6 +1870,22 @@ Returning From a Procedure
  then NULL must be returned.  Returning any other value
  will result in an error.
 
+
+
+ If a procedure has output parameters, then the output values can be
+ assigned to the parameters as if they were varia

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-05 19:38 GMT+01:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

> On 3/5/18 11:00, Pavel Stehule wrote:
> > I am looking on attached code, and it looks pretty well. Can be really
> > nice if this code will be part of release 11, because it is very
> > interesting, important feature feature.
>
> Here is an updated patch, rebased on top of several recent changes, also
> added more documentation and tests in other PLs.
>
>
why just OUT variables are disallowed?

The oracle initializes these values to NULL - we can do same?

Minimally this message is not too friendly, there should be hint - "only
INOUT is suported" - but better support OUT too - from TOP OUT variables
should not be passed. from PL should be required.

I wrote recursive procedure. The call finished by exception. Why?


 create or replace procedure p(x int,inout a int, inout b numeric)
as $$
begin
raise notice 'xxx % %', a, b;
if (x > 1) then
  a := x / 10;
  b := x / 2;
  call p(b::int, a, b);
end if;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call p(100, -1, -1);
NOTICE:  xxx -1 -1
NOTICE:  xxx 10 50
NOTICE:  xxx 5 25
NOTICE:  xxx 2 12
NOTICE:  xxx 1 6
NOTICE:  xxx 0 3
NOTICE:  xxx 0 1
ERROR:  unsupported target
CONTEXT:  PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL

Because these variables are INOUT then it should work.

This issue can be detected in compile time, maybe?

postgres=# create or replace procedure p(x int,inout a int, inout b numeric)
as $$
begin raise notice 'xxx % %', a, b;if (x > 1) then
  a := x / 10;
  b := x / 2; call p(b::int, a, 10); <--- can be detected in compile time?
end if;
end;
$$ language plpgsql;

Is terrible, how this patch is short.

Regards

Pavel






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

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-05 19:41 GMT+01:00 Pavel Stehule :

>
>
> 2018-03-05 19:38 GMT+01:00 Peter Eisentraut  com>:
>
>> On 3/5/18 11:00, Pavel Stehule wrote:
>> > I am looking on attached code, and it looks pretty well. Can be really
>> > nice if this code will be part of release 11, because it is very
>> > interesting, important feature feature.
>>
>> Here is an updated patch, rebased on top of several recent changes, also
>> added more documentation and tests in other PLs.
>>
>> > p.s. can be nice, if we allow same trick with calling of OUT variables
>> > functions in plpgsql
>> >
>> > fx(in a, out x, out y) return int -- but requires some special mark
>> >
>> > do $$
>> > declare x int, y int, z int;
>> > begin
>> >   z := fx(10, x, y);
>> >   raise notice '% 
>> >
>> > Then migration from Oracle can be really easy and friendly
>>
>> This would require some changes to how routines are looked up, because
>> we currently ignore OUT parameters there.  That code does not exist yet.
>>  But it's certainly a plausible extension for the future.
>>
>
> sure - this is topic for 12 release. But it can fix more than one issue
> when PL/SQL code is migrated.
>
> note: in this case we should to return one parameter more. Out parameters
> + RETURN expression result.
>

this problem is simple/difficult. the type of function can be detected from
call context - when function is called with assigned out variable(s) (all
OUT variables should be assigned), then the behave should be classical -
and RETURN expression for non void functions should be required. Else, the
OUT variables should not be assigned, and function will be called in
postgresql style - the function returns tuple defined by OUT parameters and
RETURN expression is prohibited. Some hint can be returning type - if it is
not defined, then result is defined just by OUT variables, when it is
defined (and it is not RECORD), then RETURN expression is required. When
RETURNS type is RECORD, then we know nothing and decision should be based
on calling context.



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

Re: INOUT parameters in procedures

[Pavel Stehule]

2018-03-05 19:38 GMT+01:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

> On 3/5/18 11:00, Pavel Stehule wrote:
> > I am looking on attached code, and it looks pretty well. Can be really
> > nice if this code will be part of release 11, because it is very
> > interesting, important feature feature.
>
> Here is an updated patch, rebased on top of several recent changes, also
> added more documentation and tests in other PLs.
>
> > p.s. can be nice, if we allow same trick with calling of OUT variables
> > functions in plpgsql
> >
> > fx(in a, out x, out y) return int -- but requires some special mark
> >
> > do $$
> > declare x int, y int, z int;
> > begin
> >   z := fx(10, x, y);
> >   raise notice '% 
> >
> > Then migration from Oracle can be really easy and friendly
>
> This would require some changes to how routines are looked up, because
> we currently ignore OUT parameters there.  That code does not exist yet.
>  But it's certainly a plausible extension for the future.
>

sure - this is topic for 12 release. But it can fix more than one issue
when PL/SQL code is migrated.

note: in this case we should to return one parameter more. Out parameters +
RETURN expression result.

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

Re: INOUT parameters in procedures

[Peter Eisentraut]

On 3/5/18 11:00, Pavel Stehule wrote:
> I am looking on attached code, and it looks pretty well. Can be really
> nice if this code will be part of release 11, because it is very
> interesting, important feature feature.

Here is an updated patch, rebased on top of several recent changes, also
added more documentation and tests in other PLs.

> p.s. can be nice, if we allow same trick with calling of OUT variables
> functions in plpgsql
> 
> fx(in a, out x, out y) return int -- but requires some special mark
> 
> do $$
> declare x int, y int, z int;
> begin
>   z := fx(10, x, y);
>   raise notice '% 
> 
> Then migration from Oracle can be really easy and friendly

This would require some changes to how routines are looked up, because
we currently ignore OUT parameters there.  That code does not exist yet.
 But it's certainly a plausible extension for the future.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 8ce8ae9e59611e1a01f7507a6595f50416b761cc Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 5 Mar 2018 12:45:33 -0500
Subject: [PATCH v2] Support INOUT parameters in procedures

In a top-level CALL, the values of INOUT parameters will be returned as
a result row.  In PL/pgSQL, the values are assigned back to the input
parameters.  In other languages, the same convention as for return a
record from a function is used.  That does not require any code changes
in the PL implementations.
---
 doc/src/sgml/plperl.sgml   |  14 +++
 doc/src/sgml/plpgsql.sgml  |  16 +++
 doc/src/sgml/plpython.sgml |  11 ++
 doc/src/sgml/pltcl.sgml|  12 +++
 doc/src/sgml/ref/create_procedure.sgml |   5 +-
 src/backend/catalog/pg_proc.c  |   3 +-
 src/backend/commands/functioncmds.c|  48 +++--
 src/backend/tcop/utility.c |   3 +-
 src/backend/utils/fmgr/funcapi.c   |  11 +-
 src/include/commands/defrem.h  |   3 +-
 src/include/funcapi.h  |   3 +-
 src/pl/plperl/expected/plperl_call.out |  25 +
 src/pl/plperl/sql/plperl_call.sql  |  22 
 src/pl/plpgsql/src/expected/plpgsql_call.out   |  71 +
 .../plpgsql/src/expected/plpgsql_transaction.out   |   2 +-
 src/pl/plpgsql/src/pl_comp.c   |  10 +-
 src/pl/plpgsql/src/pl_exec.c   | 118 +
 src/pl/plpgsql/src/pl_funcs.c  |  25 +
 src/pl/plpgsql/src/pl_gram.y   |  38 +--
 src/pl/plpgsql/src/pl_scanner.c|   1 +
 src/pl/plpgsql/src/plpgsql.h   |  12 +++
 src/pl/plpgsql/src/sql/plpgsql_call.sql|  66 
 src/pl/plpython/expected/plpython_call.out |  23 
 src/pl/plpython/plpy_exec.c|  24 ++---
 src/pl/plpython/sql/plpython_call.sql  |  20 
 src/pl/tcl/expected/pltcl_call.out |  26 +
 src/pl/tcl/sql/pltcl_call.sql  |  23 
 27 files changed, 588 insertions(+), 47 deletions(-)

diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index cff7a847de..9295c03db9 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -278,6 +278,20 @@ PL/Perl Functions and Arguments
hash will be returned as null values.
   
 
+  
+   Similarly, output parameters of procedures can be returned as a hash
+   reference:
+
+
+CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
+my ($a, $b) = @_;
+return {a = $a * 3, b = $b * 3};
+$$ LANGUAGE plperl;
+
+CALL perl_triple(5, 10);
+
+  
+
   
 PL/Perl functions can also return sets of either scalar or
 composite types.  Usually you'll want to return rows one at a
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c1e3c6a19d..6c25116538 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1870,6 +1870,22 @@ Returning From a Procedure
  then NULL must be returned.  Returning any other value
  will result in an error.
 
+
+
+ If a procedure has output parameters, then the output values can be
+ assigned to the parameters as if they were variables.  For example:
+
+CREATE PROCEDURE triple(INOUT x int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+x := x * 3;
+END;
+$$;
+
+CALL triple(5);
+
+

 

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index ba79beb743..3b7974690e 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -649,6 +649,17 @@ Composite Types
 $$ LANGUAGE plpythonu;
 
 SELECT * FROM multiout_simple();
+
+   
+
+   
+Output parameters of procedures are pass

Re: INOUT parameters in procedures

[Douglas Doole]

>
> At the top-level, it's even more dubious.  In DB2, apparently you write
>>
>> CALL foo(123, ?);
>>
>> with a literal ? for the OUT parameters.
>>
>
That's not actually as scary as it seems.

DB2 has two cases where you can use a ? like that:

1) In CLP (DB2's equivalent to psql)

DB2 draws a distinct line between procedures and functions, and you have to
invoke procedures with CALL FOO(...). Since CLP doesn't support variables
(and SQL variables didn't exist in DB2 when the CALL statement was
introduced), they needed a way to say "there's an output parameter here" so
they settled on using ? as the placeholder. (? was chosen because it ties
nicely into the next point.)

2) In dynamic SQL

DB2 has traditionally used ? as a parameter marker (placeholder for a
variable) in dynamic SQL. So the usage would look something like:

DECLARE res INTEGER;
DECLARE text VARCHAR(50);

SET text = 'CALL foo(123, ?)';
PREPARE stmt FROM text;
EXECUTE stmt INTO res; -- This invokes the statement and maps the ? into
the variable "res"

If you didn't need/want to use dynamic SQL, then you could have simply
written:

CALL foo(123, res);

- Doug Doole
Salesforce

Re: INOUT parameters in procedures

[Pavel Stehule]

Hi

2018-02-28 23:28 GMT+01:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

> 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.)
>
>
> So ... no OUT parameters, though.  I'm struggling to find a way to make
> this compatible with everything else.  For functions, the OUT parameters
> don't appear in the signature.  But that is not how this is specified in
> the SQL standard for procedures (I think).  In PL/pgSQL, you'd expect that
>
> CREATE PROCEDURE foo(a int, OUT b int) ...
>
> could be called like
>
> CALL foo(x, y);
>
> but that would require a different way of parsing function invocation.
>
> At the top-level, it's even more dubious.  In DB2, apparently you write
>
> CALL foo(123, ?);
>
> with a literal ? for the OUT parameters.
>
> In Oracle, I've seen CALL ... INTO syntax.
>
> Anyway, I'm leaving this out for now.  It can be worked around by using
> INOUT parameters.  Future improvements would be mainly syntax/parsing
> adjustments; the guts that I'm implementing here would remain valid.
>

I am looking on attached code, and it looks pretty well. Can be really nice
if this code will be part of release 11, because it is very interesting,
important feature feature.

Regards

p.s. can be nice, if we allow same trick with calling of OUT variables
functions in plpgsql

fx(in a, out x, out y) return int -- but requires some special mark

do $$
declare x int, y int, z int;
begin
  z := fx(10, x, y);
  raise notice '% 

Then migration from Oracle can be really easy and friendly





Pavel

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

INOUT parameters in procedures

[Peter Eisentraut]

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


So ... no OUT parameters, though.  I'm struggling to find a way to make
this compatible with everything else.  For functions, the OUT parameters
don't appear in the signature.  But that is not how this is specified in
the SQL standard for procedures (I think).  In PL/pgSQL, you'd expect that

CREATE PROCEDURE foo(a int, OUT b int) ...

could be called like

CALL foo(x, y);

but that would require a different way of parsing function invocation.

At the top-level, it's even more dubious.  In DB2, apparently you write

CALL foo(123, ?);

with a literal ? for the OUT parameters.

In Oracle, I've seen CALL ... INTO syntax.

Anyway, I'm leaving this out for now.  It can be worked around by using
INOUT parameters.  Future improvements would be mainly syntax/parsing
adjustments; the guts that I'm implementing here would remain valid.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 127f3716a28cceca5077786e2cb3717e36dbb426 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 27 Feb 2018 09:55:32 -0500
Subject: [PATCH v1 1/2] fixup! Add prokind column, replacing proisagg and
 proiswindow

---
 src/backend/commands/dropcmds.c |  2 +-
 src/backend/parser/parse_func.c |  6 +++---
 src/backend/utils/cache/lsyscache.c | 12 ++--
 src/include/utils/lsyscache.h   |  2 +-
 4 files changed, 11 insertions(+), 11 deletions(-)

diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index fc4ce8d22a..45493abf57 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -92,7 +92,7 @@ RemoveObjects(DropStmt *stmt)
 */
if (stmt->removeType == OBJECT_FUNCTION)
{
-   if (get_func_isagg(address.objectId))
+   if (get_func_kind(address.objectId) == 
PROKIND_AGGREGATE)
ereport(ERROR,

(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 errmsg("\"%s\" is an aggregate 
function",
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9dbf2c2b63..0b5145f70d 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2078,7 +2078,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs 
*func, bool noError)
if (objtype == OBJECT_FUNCTION)
{
/* Make sure it's a function, not a procedure */
-   if (oid && get_func_rettype(oid) == InvalidOid)
+   if (oid && get_func_kind(oid) == PROKIND_PROCEDURE)
{
if (noError)
return InvalidOid;
@@ -2109,7 +2109,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs 
*func, bool noError)
}
 
/* Make sure it's a procedure */
-   if (get_func_rettype(oid) != InvalidOid)
+   if (get_func_kind(oid) != PROKIND_PROCEDURE)
{
if (noError)
return InvalidOid;
@@ -2145,7 +2145,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs 
*func, bool noError)
}
 
/* Make sure it's an aggregate */
-   if (!get_func_isagg(oid))
+   if (get_func_kind(oid) != PROKIND_AGGREGATE)
{
if (noError)
return InvalidOid;
diff --git a/src/backend/utils/cache/lsyscache.c 
b/src/backend/utils/cache/lsyscache.c
index 161470aa34..869a937d5a 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1600,20 +1600,20 @@ func_parallel(Oid funcid)
 }
 
 /*
- * get_func_isagg
- *Given procedure id, return whether the function is an aggregate.
+ * get_func_kind
+ *Given procedure id, return the function kind (prokind).
  */
-bool
-get_func_isagg(Oid funcid)
+char
+get_func_kind(Oid funcid)
 {
HeapTuple   tp;
-   boolresult;
+   charresult;
 
tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
if (!HeapTupleIsValid(tp))
elog(ERROR, "cache lookup failed for function %u", funcid);
 
-   result = ((Form_pg_proc) GETSTRUCT(tp))->pro