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
From 27aa494f2f538d8d267841d5bd524b0eb3c77adb Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 26 Aug 2020 23:11:55 +0200
Subject: [PATCH v1] Support for OUT parameters in procedures
Unlike for functions, OUT parameters for procedures are part of the
signature. Therefore, they have to be listed in pg_proc.proargtypes
as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.
---
doc/src/sgml/catalogs.sgml | 5 +-
doc/src/sgml/plpgsql.sgml | 38 ++++++++
doc/src/sgml/ref/alter_extension.sgml | 11 ++-
doc/src/sgml/ref/alter_procedure.sgml | 5 +-
doc/src/sgml/ref/comment.sgml | 11 ++-
doc/src/sgml/ref/create_procedure.sgml | 6 +-
doc/src/sgml/ref/drop_procedure.sgml | 5 +-
doc/src/sgml/ref/security_label.sgml | 11 ++-
doc/src/sgml/xfunc.sgml | 59 ++++++++++++
src/backend/commands/functioncmds.c | 52 ++++++----
src/backend/parser/gram.y | 96 ++++++++++++-------
src/include/catalog/pg_proc.h | 2 +-
src/pl/plperl/expected/plperl_call.out | 18 ++++
src/pl/plperl/sql/plperl_call.sql | 20 ++++
src/pl/plpgsql/src/expected/plpgsql_call.out | 19 ++++
src/pl/plpgsql/src/pl_comp.c | 1 +
src/pl/plpgsql/src/sql/plpgsql_call.sql | 21 ++++
src/pl/plpython/expected/plpython_call.out | 17 ++++
src/pl/plpython/plpy_procedure.c | 4 +-
src/pl/plpython/sql/plpython_call.sql | 19 ++++
src/pl/tcl/expected/pltcl_call.out | 17 ++++
src/pl/tcl/sql/pltcl_call.sql | 19 ++++
.../regress/expected/create_procedure.out | 16 +++-
src/test/regress/sql/create_procedure.sql | 13 ++-
24 files changed, 400 insertions(+), 85 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9fe260ecff..a071154123 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5865,8 +5865,9 @@ <title><structname>pg_proc</structname> Columns</title>
<para>
An array with the data types of the function arguments. This includes
only input arguments (including <literal>INOUT</literal> and
- <literal>VARIADIC</literal> arguments), and thus represents
- the call signature of the function.
+ <literal>VARIADIC</literal> arguments), as well as
+ <literal>OUT</literal> parameters of procedures, and thus represents
+ the call signature of the function or procedure.
</para></entry>
</row>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 815912666d..309a714fc4 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -478,6 +478,14 @@ <title>Declaring Function Parameters</title>
included it, but it would be redundant.
</para>
+ <para>
+ To call a function with <literal>OUT</literal> parameters, omit the
+ output parameter in the function call:
+<programlisting>
+SELECT sales_tax(100.00);
+</programlisting>
+ </para>
+
<para>
Output parameters are most useful when returning multiple values.
A trivial example is:
@@ -489,6 +497,11 @@ <title>Declaring Function Parameters</title>
prod := x * y;
END;
$$ LANGUAGE plpgsql;
+
+SELECT * FROM sum_n_product(2, 4);
+ sum | prod
+-----+------
+ 6 | 8
</programlisting>
As discussed in <xref linkend="xfunc-output-parameters"/>, this
@@ -497,6 +510,31 @@ <title>Declaring Function Parameters</title>
<literal>RETURNS record</literal>.
</para>
+ <para>
+ This also works with procedures, for example:
+
+<programlisting>
+CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
+BEGIN
+ sum := x + y;
+ prod := x * y;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+ In a call to a procedure, all the parameters must be specified. For
+ output parameters, <literal>NULL</literal> may be specified.
+<programlisting>
+CALL sum_n_product(2, 4, NULL, NULL);
+ sum | prod
+-----+------
+ 6 | 8
+</programlisting>
+ Output parameters in procedures become more interesting in nested calls,
+ where they can be assigned to variables. See <xref
+ linkend="plpgsql-statements-calling-procedure"/> for details.
+ </para>
+
<para>
Another way to declare a <application>PL/pgSQL</application> function
is with <literal>RETURNS TABLE</literal>, for example:
diff --git a/doc/src/sgml/ref/alter_extension.sgml
b/doc/src/sgml/ref/alter_extension.sgml
index a2d405d6cd..7aaa16a089 100644
--- a/doc/src/sgml/ref/alter_extension.sgml
+++ b/doc/src/sgml/ref/alter_extension.sgml
@@ -212,11 +212,12 @@ <title>Parameters</title>
argument: <literal>IN</literal>, <literal>OUT</literal>,
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
If omitted, the default is <literal>IN</literal>.
- Note that <command>ALTER EXTENSION</command> does not actually pay
- any attention to <literal>OUT</literal> arguments, since only the input
- arguments are needed to determine the function's identity.
- So it is sufficient to list the <literal>IN</literal>,
<literal>INOUT</literal>,
- and <literal>VARIADIC</literal> arguments.
+ Note that <command>ALTER EXTENSION</command> does not actually pay any
+ attention to <literal>OUT</literal> arguments for functions and
+ aggregates (but not procedures), since only the input arguments are
+ needed to determine the function's identity. So it is sufficient to
+ list the <literal>IN</literal>, <literal>INOUT</literal>, and
+ <literal>VARIADIC</literal> arguments for functions and aggregates.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/alter_procedure.sgml
b/doc/src/sgml/ref/alter_procedure.sgml
index dae80076d9..86079e36b9 100644
--- a/doc/src/sgml/ref/alter_procedure.sgml
+++ b/doc/src/sgml/ref/alter_procedure.sgml
@@ -81,8 +81,9 @@ <title>Parameters</title>
<listitem>
<para>
- The mode of an argument: <literal>IN</literal> or
<literal>VARIADIC</literal>.
- If omitted, the default is <literal>IN</literal>.
+ The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
+ <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
+ the default is <literal>IN</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index fd7492a255..f4b8ee0fbd 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -178,11 +178,12 @@ <title>Parameters</title>
argument: <literal>IN</literal>, <literal>OUT</literal>,
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
If omitted, the default is <literal>IN</literal>.
- Note that <command>COMMENT</command> does not actually pay
- any attention to <literal>OUT</literal> arguments, since only the input
- arguments are needed to determine the function's identity.
- So it is sufficient to list the <literal>IN</literal>,
<literal>INOUT</literal>,
- and <literal>VARIADIC</literal> arguments.
+ Note that <command>COMMENT</command> does not actually pay any attention
+ to <literal>OUT</literal> arguments for functions and aggregates (but
+ not procedures), since only the input arguments are needed to determine
+ the function's identity. So it is sufficient to list the
+ <literal>IN</literal>, <literal>INOUT</literal>, and
+ <literal>VARIADIC</literal> arguments for functions and aggregates.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_procedure.sgml
b/doc/src/sgml/ref/create_procedure.sgml
index 0ea6513cb5..c7ce798002 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -97,11 +97,9 @@ <title>Parameters</title>
<listitem>
<para>
- The mode of an argument: <literal>IN</literal>,
+ The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
- the default is <literal>IN</literal>. (<literal>OUT</literal>
- arguments are currently not supported for procedures. Use
- <literal>INOUT</literal> instead.)
+ the default is <literal>IN</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_procedure.sgml
b/doc/src/sgml/ref/drop_procedure.sgml
index 6da266ae2d..bf2c6ce1aa 100644
--- a/doc/src/sgml/ref/drop_procedure.sgml
+++ b/doc/src/sgml/ref/drop_procedure.sgml
@@ -67,8 +67,9 @@ <title>Parameters</title>
<listitem>
<para>
- The mode of an argument: <literal>IN</literal> or
<literal>VARIADIC</literal>.
- If omitted, the default is <literal>IN</literal>.
+ The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
+ <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
+ the default is <literal>IN</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/security_label.sgml
b/doc/src/sgml/ref/security_label.sgml
index e9688cce21..9b87bcd519 100644
--- a/doc/src/sgml/ref/security_label.sgml
+++ b/doc/src/sgml/ref/security_label.sgml
@@ -127,11 +127,12 @@ <title>Parameters</title>
argument: <literal>IN</literal>, <literal>OUT</literal>,
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
If omitted, the default is <literal>IN</literal>.
- Note that <command>SECURITY LABEL</command> does not actually
- pay any attention to <literal>OUT</literal> arguments, since only the
input
- arguments are needed to determine the function's identity.
- So it is sufficient to list the <literal>IN</literal>,
<literal>INOUT</literal>,
- and <literal>VARIADIC</literal> arguments.
+ Note that <command>SECURITY LABEL</command> does not actually pay any
+ attention to <literal>OUT</literal> arguments for functions and
+ aggregates (but not procedures), since only the input arguments are
+ needed to determine the function's identity. So it is sufficient to
+ list the <literal>IN</literal>, <literal>INOUT</literal>, and
+ <literal>VARIADIC</literal> arguments for functions and aggregates.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6de464c654..b713052003 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -176,6 +176,24 @@ <title>Query Language (<acronym>SQL</acronym>)
Functions</title>
</screen>
</para>
+ <para>
+ You can also write this as a procedure, thus avoiding the issue of the
+ return type. For example:
+<screen>
+CREATE PROCEDURE clean_emp() AS '
+ DELETE FROM emp
+ WHERE salary < 0;
+' LANGUAGE SQL;
+
+CALL clean_emp();
+</screen>
+ In simple cases like this, the difference between a function returning
+ <type>void</type> and a procedure is mostly stylistic. However,
+ procedures offer additional functionality such as transaction control
+ that is not available in functions. Also, procedures are SQL standard
+ whereas returning <type>void</type> is a PostgreSQL extension.
+ </para>
+
<note>
<para>
The entire body of a SQL function is parsed before any of it is
@@ -713,6 +731,47 @@ <title><acronym>SQL</acronym> Functions with Output
Parameters</title>
</para>
</sect2>
+ <sect2 id="xfunc-output-parameters-proc">
+ <title><acronym>SQL</acronym> Procedures with Output Parameters</title>
+
+ <indexterm>
+ <primary>procedures</primary>
+ <secondary>output parameter</secondary>
+ </indexterm>
+
+ <para>
+ Output parameters are also supported in procedures, but they work a bit
+ differently from functions. Notably, output parameters
+ <emphasis>are</emphasis> included in the signature of a procedure and
+ must be specified in the procedure call.
+ </para>
+
+ <para>
+ For example, the bank account debiting routine from earlier could be
+ written like this:
+<programlisting>
+CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance
numeric) AS $$
+ UPDATE bank
+ SET balance = balance - debit
+ WHERE accountno = tp1.accountno
+ RETURNING balance;
+$$ LANGUAGE SQL;
+</programlisting>
+ To call this procedure, it is irrelevant what is passed as the argument
+ of the <literal>OUT</literal> parameter, so you could pass
+ <literal>NULL</literal>:
+<programlisting>
+CALL tp1(17, 100.0, NULL);
+</programlisting>
+ </para>
+
+ <para>
+ Procedures with output parameters are more useful in PL/pgSQL, where the
+ output parameters can be assigned to variables. See <xref
+ linkend="plpgsql-statements-calling-procedure"/> for details.
+ </para>
+ </sect2>
+
<sect2 id="xfunc-sql-variadic-functions">
<title><acronym>SQL</acronym> Functions with Variable Numbers of
Arguments</title>
diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index 1b5bdcec8b..c7f47ec4c1 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -194,8 +194,8 @@ interpret_function_parameter_list(ParseState *pstate,
Oid
*requiredResultType)
{
int parameterCount = list_length(parameters);
- Oid *inTypes;
- int inCount = 0;
+ Oid *sigArgTypes;
+ int sigArgCount = 0;
Datum *allTypes;
Datum *paramModes;
Datum *paramNames;
@@ -209,7 +209,7 @@ interpret_function_parameter_list(ParseState *pstate,
*variadicArgType = InvalidOid; /* default result */
*requiredResultType = InvalidOid; /* default result */
- inTypes = (Oid *) palloc(parameterCount * sizeof(Oid));
+ sigArgTypes = (Oid *) palloc(parameterCount * sizeof(Oid));
allTypes = (Datum *) palloc(parameterCount * sizeof(Datum));
paramModes = (Datum *) palloc(parameterCount * sizeof(Datum));
paramNames = (Datum *) palloc0(parameterCount * sizeof(Datum));
@@ -281,15 +281,6 @@ interpret_function_parameter_list(ParseState *pstate,
errmsg("functions cannot
accept set arguments")));
}
- if (objtype == OBJECT_PROCEDURE)
- {
- if (fp->mode == FUNC_PARAM_OUT)
- ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("procedures cannot have
OUT arguments"),
- errhint("INOUT arguments are
permitted.")));
- }
-
/* handle input parameters */
if (fp->mode != FUNC_PARAM_OUT && fp->mode != FUNC_PARAM_TABLE)
{
@@ -298,10 +289,16 @@ interpret_function_parameter_list(ParseState *pstate,
ereport(ERROR,
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
errmsg("VARIADIC parameter
must be the last input parameter")));
- inTypes[inCount++] = toid;
isinput = true;
}
+ if (fp->mode == FUNC_PARAM_IN || fp->mode == FUNC_PARAM_INOUT ||
+ (objtype == OBJECT_PROCEDURE && fp->mode ==
FUNC_PARAM_OUT) ||
+ fp->mode == FUNC_PARAM_VARIADIC)
+ {
+ sigArgTypes[sigArgCount++] = toid;
+ }
+
/* handle output parameters */
if (fp->mode != FUNC_PARAM_IN && fp->mode !=
FUNC_PARAM_VARIADIC)
{
@@ -429,7 +426,7 @@ interpret_function_parameter_list(ParseState *pstate,
}
/* Now construct the proper outputs as needed */
- *parameterTypes = buildoidvector(inTypes, inCount);
+ *parameterTypes = buildoidvector(sigArgTypes, sigArgCount);
if (outCount > 0 || varCount > 0)
{
@@ -2071,6 +2068,9 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
int nargs;
int i;
AclResult aclresult;
+ Oid *argtypes;
+ char **argnames;
+ char *argmodes;
FmgrInfo flinfo;
CallContext *callcontext;
EState *estate;
@@ -2131,6 +2131,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
tp);
nargs = list_length(fexpr->args);
+ get_func_arg_info(tp, &argtypes, &argnames, &argmodes);
+
ReleaseSysCache(tp);
/* safety check; see ExecInitFunc() */
@@ -2160,16 +2162,24 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
i = 0;
foreach(lc, fexpr->args)
{
- ExprState *exprstate;
- Datum val;
- bool isnull;
+ if (argmodes && argmodes[i] == PROARGMODE_OUT)
+ {
+ fcinfo->args[i].value = 0;
+ fcinfo->args[i].isnull = true;
+ }
+ else
+ {
+ ExprState *exprstate;
+ Datum val;
+ bool isnull;
- exprstate = ExecPrepareExpr(lfirst(lc), estate);
+ exprstate = ExecPrepareExpr(lfirst(lc), estate);
- val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull);
+ val = ExecEvalExprSwitchContext(exprstate, econtext,
&isnull);
- fcinfo->args[i].value = val;
- fcinfo->args[i].isnull = isnull;
+ fcinfo->args[i].value = val;
+ fcinfo->args[i].isnull = isnull;
+ }
i++;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dbb47d4982..d0ea5d2d9b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -166,7 +166,7 @@ static RoleSpec *makeRoleSpec(RoleSpecType type, int
location);
static void check_qualified_name(List *names, core_yyscan_t yyscanner);
static List *check_func_name(List *names, core_yyscan_t yyscanner);
static List *check_indirection(List *indirection, core_yyscan_t yyscanner);
-static List *extractArgTypes(List *parameters);
+static List *extractArgTypes(ObjectType objtype, List *parameters);
static List *extractAggrArgTypes(List *aggrargs);
static List *makeOrderedSetArgs(List *directargs, List *orderedargs,
core_yyscan_t
yyscanner);
@@ -375,8 +375,8 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <accesspriv> privilege
%type <list> privileges privilege_list
%type <privtarget> privilege_target
-%type <objwithargs> function_with_argtypes aggregate_with_argtypes
operator_with_argtypes
-%type <list> function_with_argtypes_list aggregate_with_argtypes_list
operator_with_argtypes_list
+%type <objwithargs> function_with_argtypes aggregate_with_argtypes
operator_with_argtypes procedure_with_argtypes function_with_argtypes_common
+%type <list> function_with_argtypes_list aggregate_with_argtypes_list
operator_with_argtypes_list procedure_with_argtypes_list
%type <ival> defacl_privilege_target
%type <defelt> DefACLOption
%type <list> DefACLOptionList
@@ -4626,7 +4626,7 @@ AlterExtensionContentsStmt:
n->object = (Node *)
lcons(makeString($9), $7);
$$ = (Node *)n;
}
- | ALTER EXTENSION name add_drop PROCEDURE
function_with_argtypes
+ | ALTER EXTENSION name add_drop PROCEDURE
procedure_with_argtypes
{
AlterExtensionContentsStmt *n =
makeNode(AlterExtensionContentsStmt);
n->extname = $3;
@@ -4635,7 +4635,7 @@ AlterExtensionContentsStmt:
n->object = (Node *) $6;
$$ = (Node *)n;
}
- | ALTER EXTENSION name add_drop ROUTINE
function_with_argtypes
+ | ALTER EXTENSION name add_drop ROUTINE
procedure_with_argtypes
{
AlterExtensionContentsStmt *n =
makeNode(AlterExtensionContentsStmt);
n->extname = $3;
@@ -6368,7 +6368,7 @@ CommentStmt:
n->comment = $8;
$$ = (Node *) n;
}
- | COMMENT ON PROCEDURE function_with_argtypes IS
comment_text
+ | COMMENT ON PROCEDURE procedure_with_argtypes IS
comment_text
{
CommentStmt *n = makeNode(CommentStmt);
n->objtype = OBJECT_PROCEDURE;
@@ -6376,7 +6376,7 @@ CommentStmt:
n->comment = $6;
$$ = (Node *) n;
}
- | COMMENT ON ROUTINE function_with_argtypes IS
comment_text
+ | COMMENT ON ROUTINE procedure_with_argtypes IS
comment_text
{
CommentStmt *n = makeNode(CommentStmt);
n->objtype = OBJECT_ROUTINE;
@@ -6522,7 +6522,7 @@ SecLabelStmt:
n->label = $9;
$$ = (Node *) n;
}
- | SECURITY LABEL opt_provider ON PROCEDURE
function_with_argtypes
+ | SECURITY LABEL opt_provider ON PROCEDURE
procedure_with_argtypes
IS security_label
{
SecLabelStmt *n =
makeNode(SecLabelStmt);
@@ -6883,7 +6883,7 @@ privilege_target:
n->objs = $2;
$$ = n;
}
- | PROCEDURE function_with_argtypes_list
+ | PROCEDURE procedure_with_argtypes_list
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->targtype = ACL_TARGET_OBJECT;
@@ -6891,7 +6891,7 @@ privilege_target:
n->objs = $2;
$$ = n;
}
- | ROUTINE function_with_argtypes_list
+ | ROUTINE procedure_with_argtypes_list
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->targtype = ACL_TARGET_OBJECT;
@@ -7412,20 +7412,33 @@ function_with_argtypes_list:
{ $$ = lappend($1, $3); }
;
+procedure_with_argtypes_list:
+ procedure_with_argtypes
{ $$ = list_make1($1); }
+ | procedure_with_argtypes_list ','
procedure_with_argtypes
+
{ $$ = lappend($1, $3); }
+ ;
+
function_with_argtypes:
func_name func_args
{
ObjectWithArgs *n =
makeNode(ObjectWithArgs);
n->objname = $1;
- n->objargs = extractArgTypes($2);
+ n->objargs =
extractArgTypes(OBJECT_FUNCTION, $2);
$$ = n;
}
+ | function_with_argtypes_common
+ {
+ $$ = $1;
+ }
+ ;
+
+function_with_argtypes_common:
/*
* Because of reduce/reduce conflicts, we can't use
func_name
* below, but we can write it out the long way, which
actually
* allows more cases.
*/
- | type_func_name_keyword
+ type_func_name_keyword
{
ObjectWithArgs *n =
makeNode(ObjectWithArgs);
n->objname =
list_make1(makeString(pstrdup($1)));
@@ -7449,6 +7462,24 @@ function_with_argtypes:
}
;
+/*
+ * This is different from function_with_argtypes in the call to
+ * extractArgTypes().
+ */
+procedure_with_argtypes:
+ func_name func_args
+ {
+ ObjectWithArgs *n =
makeNode(ObjectWithArgs);
+ n->objname = $1;
+ n->objargs =
extractArgTypes(OBJECT_PROCEDURE, $2);
+ $$ = n;
+ }
+ | function_with_argtypes_common
+ {
+ $$ = $1;
+ }
+ ;
+
/*
* func_args_with_defaults is separate because we only want to accept
* defaults in CREATE FUNCTION, not in ALTER etc.
@@ -7827,7 +7858,7 @@ AlterFunctionStmt:
n->actions = $4;
$$ = (Node *) n;
}
- | ALTER PROCEDURE function_with_argtypes
alterfunc_opt_list opt_restrict
+ | ALTER PROCEDURE procedure_with_argtypes
alterfunc_opt_list opt_restrict
{
AlterFunctionStmt *n =
makeNode(AlterFunctionStmt);
n->objtype = OBJECT_PROCEDURE;
@@ -7835,7 +7866,7 @@ AlterFunctionStmt:
n->actions = $4;
$$ = (Node *) n;
}
- | ALTER ROUTINE function_with_argtypes
alterfunc_opt_list opt_restrict
+ | ALTER ROUTINE procedure_with_argtypes
alterfunc_opt_list opt_restrict
{
AlterFunctionStmt *n =
makeNode(AlterFunctionStmt);
n->objtype = OBJECT_ROUTINE;
@@ -7891,7 +7922,7 @@ RemoveFuncStmt:
n->concurrent = false;
$$ = (Node *)n;
}
- | DROP PROCEDURE function_with_argtypes_list
opt_drop_behavior
+ | DROP PROCEDURE procedure_with_argtypes_list
opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = OBJECT_PROCEDURE;
@@ -7901,7 +7932,7 @@ RemoveFuncStmt:
n->concurrent = false;
$$ = (Node *)n;
}
- | DROP PROCEDURE IF_P EXISTS
function_with_argtypes_list opt_drop_behavior
+ | DROP PROCEDURE IF_P EXISTS
procedure_with_argtypes_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = OBJECT_PROCEDURE;
@@ -7911,7 +7942,7 @@ RemoveFuncStmt:
n->concurrent = false;
$$ = (Node *)n;
}
- | DROP ROUTINE function_with_argtypes_list
opt_drop_behavior
+ | DROP ROUTINE procedure_with_argtypes_list
opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = OBJECT_ROUTINE;
@@ -7921,7 +7952,7 @@ RemoveFuncStmt:
n->concurrent = false;
$$ = (Node *)n;
}
- | DROP ROUTINE IF_P EXISTS function_with_argtypes_list
opt_drop_behavior
+ | DROP ROUTINE IF_P EXISTS procedure_with_argtypes_list
opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = OBJECT_ROUTINE;
@@ -8392,7 +8423,7 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes
RENAME TO name
n->missing_ok = true;
$$ = (Node *)n;
}
- | ALTER PROCEDURE function_with_argtypes RENAME TO name
+ | ALTER PROCEDURE procedure_with_argtypes RENAME TO name
{
RenameStmt *n = makeNode(RenameStmt);
n->renameType = OBJECT_PROCEDURE;
@@ -8410,7 +8441,7 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes
RENAME TO name
n->missing_ok = false;
$$ = (Node *)n;
}
- | ALTER ROUTINE function_with_argtypes RENAME TO name
+ | ALTER ROUTINE procedure_with_argtypes RENAME TO name
{
RenameStmt *n = makeNode(RenameStmt);
n->renameType = OBJECT_ROUTINE;
@@ -8821,7 +8852,7 @@ AlterObjectDependsStmt:
n->remove = $4;
$$ = (Node *)n;
}
- | ALTER PROCEDURE function_with_argtypes opt_no DEPENDS
ON EXTENSION name
+ | ALTER PROCEDURE procedure_with_argtypes opt_no
DEPENDS ON EXTENSION name
{
AlterObjectDependsStmt *n =
makeNode(AlterObjectDependsStmt);
n->objectType = OBJECT_PROCEDURE;
@@ -8830,7 +8861,7 @@ AlterObjectDependsStmt:
n->remove = $4;
$$ = (Node *)n;
}
- | ALTER ROUTINE function_with_argtypes opt_no DEPENDS
ON EXTENSION name
+ | ALTER ROUTINE procedure_with_argtypes opt_no DEPENDS
ON EXTENSION name
{
AlterObjectDependsStmt *n =
makeNode(AlterObjectDependsStmt);
n->objectType = OBJECT_ROUTINE;
@@ -8961,7 +8992,7 @@ AlterObjectSchemaStmt:
n->missing_ok = false;
$$ = (Node *)n;
}
- | ALTER PROCEDURE function_with_argtypes SET SCHEMA name
+ | ALTER PROCEDURE procedure_with_argtypes SET SCHEMA
name
{
AlterObjectSchemaStmt *n =
makeNode(AlterObjectSchemaStmt);
n->objectType = OBJECT_PROCEDURE;
@@ -8970,7 +9001,7 @@ AlterObjectSchemaStmt:
n->missing_ok = false;
$$ = (Node *)n;
}
- | ALTER ROUTINE function_with_argtypes SET SCHEMA name
+ | ALTER ROUTINE procedure_with_argtypes SET SCHEMA name
{
AlterObjectSchemaStmt *n =
makeNode(AlterObjectSchemaStmt);
n->objectType = OBJECT_ROUTINE;
@@ -9272,7 +9303,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes
OWNER TO RoleSpec
n->newowner = $9;
$$ = (Node *)n;
}
- | ALTER PROCEDURE function_with_argtypes OWNER TO
RoleSpec
+ | ALTER PROCEDURE procedure_with_argtypes OWNER TO
RoleSpec
{
AlterOwnerStmt *n =
makeNode(AlterOwnerStmt);
n->objectType = OBJECT_PROCEDURE;
@@ -9280,7 +9311,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes
OWNER TO RoleSpec
n->newowner = $6;
$$ = (Node *)n;
}
- | ALTER ROUTINE function_with_argtypes OWNER TO RoleSpec
+ | ALTER ROUTINE procedure_with_argtypes OWNER TO
RoleSpec
{
AlterOwnerStmt *n =
makeNode(AlterOwnerStmt);
n->objectType = OBJECT_ROUTINE;
@@ -15788,13 +15819,14 @@ check_indirection(List *indirection, core_yyscan_t
yyscanner)
}
/* extractArgTypes()
+ *
* Given a list of FunctionParameter nodes, extract a list of just the
- * argument types (TypeNames) for input parameters only. This is what
- * is needed to look up an existing function, which is what is wanted by
- * the productions that use this call.
+ * argument types (TypeNames) for signature parameters only (e.g., only input
+ * parameters for functions). This is what is needed to look up an existing
+ * function, which is what is wanted by the productions that use this call.
*/
static List *
-extractArgTypes(List *parameters)
+extractArgTypes(ObjectType objtype, List *parameters)
{
List *result = NIL;
ListCell *i;
@@ -15803,7 +15835,7 @@ extractArgTypes(List *parameters)
{
FunctionParameter *p = (FunctionParameter *) lfirst(i);
- if (p->mode != FUNC_PARAM_OUT && p->mode != FUNC_PARAM_TABLE)
+ if ((p->mode != FUNC_PARAM_OUT || objtype == OBJECT_PROCEDURE)
&& p->mode != FUNC_PARAM_TABLE)
result = lappend(result, p->argType);
}
return result;
@@ -15816,7 +15848,7 @@ static List *
extractAggrArgTypes(List *aggrargs)
{
Assert(list_length(aggrargs) == 2);
- return extractArgTypes((List *) linitial(aggrargs));
+ return extractArgTypes(OBJECT_AGGREGATE, (List *) linitial(aggrargs));
}
/* makeOrderedSetArgs()
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b50fa25dbd..268c810896 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -91,7 +91,7 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP
BKI_ROWTYPE_OID(81,Proce
* proargtypes
*/
- /* parameter types (excludes OUT params) */
+ /* parameter types (excludes OUT params of functions) */
oidvector proargtypes BKI_LOOKUP(pg_type) BKI_FORCE_NOT_NULL;
#ifdef CATALOG_VARLEN
diff --git a/src/pl/plperl/expected/plperl_call.out
b/src/pl/plperl/expected/plperl_call.out
index c55c59cbce..a08b9ff795 100644
--- a/src/pl/plperl/expected/plperl_call.out
+++ b/src/pl/plperl/expected/plperl_call.out
@@ -48,6 +48,24 @@ CALL test_proc6(2, 3, 4);
6 | 8
(1 row)
+-- OUT parameters
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plperl
+AS $$
+my ($a, $b) = @_;
+elog(NOTICE, "a: $a, b: $b");
+return { b => $a * 2 };
+$$;
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+NOTICE: a: 10, b:
+NOTICE: _a: 10, _b: 20
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/plperl/sql/plperl_call.sql
b/src/pl/plperl/sql/plperl_call.sql
index 2cf5461fef..bbea85fc9f 100644
--- a/src/pl/plperl/sql/plperl_call.sql
+++ b/src/pl/plperl/sql/plperl_call.sql
@@ -51,6 +51,26 @@ CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)
CALL test_proc6(2, 3, 4);
+-- OUT parameters
+
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plperl
+AS $$
+my ($a, $b) = @_;
+elog(NOTICE, "a: $a, b: $b");
+return { b => $a * 2 };
+$$;
+
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+
+
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out
b/src/pl/plpgsql/src/expected/plpgsql_call.out
index d9c88e85c8..9738571611 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -264,6 +264,25 @@ END
$$;
ERROR: procedure parameter "c" is an output parameter but corresponding
argument is not writable
CONTEXT: PL/pgSQL function inline_code_block line 5 at CALL
+-- OUT parameters
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE NOTICE 'a: %, b: %', a, b;
+ b := a * 2;
+END;
+$$;
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+NOTICE: a: 10, b: <NULL>
+NOTICE: _a: 10, _b: 20
-- transition variable assignment
TRUNCATE test1;
CREATE FUNCTION triggerfunc1() RETURNS trigger
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index e7f4a5f291..344627da95 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -458,6 +458,7 @@ do_compile(FunctionCallInfo fcinfo,
/* Remember arguments in appropriate arrays */
if (argmode == PROARGMODE_IN ||
argmode == PROARGMODE_INOUT ||
+ (argmode == PROARGMODE_OUT &&
function->fn_prokind == PROKIND_PROCEDURE) ||
argmode == PROARGMODE_VARIADIC)
in_arg_varnos[num_in_args++] =
argvariable->dno;
if (argmode == PROARGMODE_OUT ||
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql
b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index 4702bd14d1..d506809ddb 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -237,6 +237,27 @@ CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int,
INOUT c int DEFAULT 11)
$$;
+-- OUT parameters
+
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE NOTICE 'a: %, b: %', a, b;
+ b := a * 2;
+END;
+$$;
+
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+
+
-- transition variable assignment
TRUNCATE test1;
diff --git a/src/pl/plpython/expected/plpython_call.out
b/src/pl/plpython/expected/plpython_call.out
index 07ae04e98b..c3f3c8e95e 100644
--- a/src/pl/plpython/expected/plpython_call.out
+++ b/src/pl/plpython/expected/plpython_call.out
@@ -52,6 +52,23 @@ CALL test_proc6(2, 3, 4);
6 | 8
(1 row)
+-- OUT parameters
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plpythonu
+AS $$
+plpy.notice("a: %s, b: %s" % (a, b))
+return (a * 2,)
+$$;
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+NOTICE: a: 10, b: None
+NOTICE: _a: 10, _b: 20
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index 9e15839611..ec47f52e61 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -273,7 +273,7 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool
is_trigger)
/* proc->nargs was initialized to 0 above */
for (i = 0; i < total; i++)
{
- if (modes[i] != PROARGMODE_OUT &&
+ if ((modes[i] != PROARGMODE_OUT ||
proc->is_procedure) &&
modes[i] != PROARGMODE_TABLE)
(proc->nargs)++;
}
@@ -289,7 +289,7 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool
is_trigger)
Form_pg_type argTypeStruct;
if (modes &&
- (modes[i] == PROARGMODE_OUT ||
+ ((modes[i] == PROARGMODE_OUT &&
!proc->is_procedure) ||
modes[i] == PROARGMODE_TABLE))
continue; /* skip OUT arguments */
diff --git a/src/pl/plpython/sql/plpython_call.sql
b/src/pl/plpython/sql/plpython_call.sql
index 2f792f92bd..46e89b1a9e 100644
--- a/src/pl/plpython/sql/plpython_call.sql
+++ b/src/pl/plpython/sql/plpython_call.sql
@@ -54,6 +54,25 @@ CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)
CALL test_proc6(2, 3, 4);
+-- OUT parameters
+
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE plpythonu
+AS $$
+plpy.notice("a: %s, b: %s" % (a, b))
+return (a * 2,)
+$$;
+
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+
+
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/tcl/expected/pltcl_call.out
b/src/pl/tcl/expected/pltcl_call.out
index d290c8fbd0..f0eb356cf2 100644
--- a/src/pl/tcl/expected/pltcl_call.out
+++ b/src/pl/tcl/expected/pltcl_call.out
@@ -49,6 +49,23 @@ CALL test_proc6(2, 3, 4);
6 | 8
(1 row)
+-- OUT parameters
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE pltcl
+AS $$
+elog NOTICE "a: $1, b: $2"
+return [list b [expr {$1 * 2}]]
+$$;
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+NOTICE: a: 10, b:
+NOTICE: _a: 10, _b: 20
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/tcl/sql/pltcl_call.sql b/src/pl/tcl/sql/pltcl_call.sql
index 95791d08be..963277e1fb 100644
--- a/src/pl/tcl/sql/pltcl_call.sql
+++ b/src/pl/tcl/sql/pltcl_call.sql
@@ -52,6 +52,25 @@ CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)
CALL test_proc6(2, 3, 4);
+-- OUT parameters
+
+CREATE PROCEDURE test_proc9(IN a int, OUT b int)
+LANGUAGE pltcl
+AS $$
+elog NOTICE "a: $1, b: $2"
+return [list b [expr {$1 * 2}]]
+$$;
+
+DO $$
+DECLARE _a int; _b int;
+BEGIN
+ _a := 10; _b := 30;
+ CALL test_proc9(_a, _b);
+ RAISE NOTICE '_a: %, _b: %', _a, _b;
+END
+$$;
+
+
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/test/regress/expected/create_procedure.out
b/src/test/regress/expected/create_procedure.out
index 211a42cefa..3838fa2324 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -146,6 +146,19 @@ AS $$
SELECT a = b;
$$;
CALL ptest7(least('a', 'b'), 'a');
+-- OUT parameters
+CREATE PROCEDURE ptest9(OUT a int)
+LANGUAGE SQL
+AS $$
+INSERT INTO cp_test VALUES (1, 'a');
+SELECT 1;
+$$;
+CALL ptest9(NULL);
+ a
+---
+ 1
+(1 row)
+
-- various error cases
CALL version(); -- error: not a procedure
ERROR: version() is not a procedure
@@ -165,9 +178,6 @@ CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT
INTO cp_test VALUES (
ERROR: invalid attribute in procedure definition
LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT I...
^
-CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test
VALUES (1, 'a') $$;
-ERROR: procedures cannot have OUT arguments
-HINT: INOUT arguments are permitted.
ALTER PROCEDURE ptest1(text) STRICT;
ERROR: invalid attribute in procedure definition
LINE 1: ALTER PROCEDURE ptest1(text) STRICT;
diff --git a/src/test/regress/sql/create_procedure.sql
b/src/test/regress/sql/create_procedure.sql
index 89b96d580f..2ef1c82cea 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -112,6 +112,18 @@ CREATE PROCEDURE ptest7(a text, b text)
CALL ptest7(least('a', 'b'), 'a');
+-- OUT parameters
+
+CREATE PROCEDURE ptest9(OUT a int)
+LANGUAGE SQL
+AS $$
+INSERT INTO cp_test VALUES (1, 'a');
+SELECT 1;
+$$;
+
+CALL ptest9(NULL);
+
+
-- various error cases
CALL version(); -- error: not a procedure
@@ -119,7 +131,6 @@ CREATE PROCEDURE ptest7(a text, b text)
CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES
(1, 'a') $$;
CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES
(1, 'a') $$;
-CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test
VALUES (1, 'a') $$;
ALTER PROCEDURE ptest1(text) STRICT;
ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function
--
2.28.0
