From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Support for OUT parameters in procedures
Date: Mon, 28 Sep 2020 12:43:39 -0400
Message-ID: <4f733cca-5e07-e167-8b38-05b5c9066d04@2ndQuadrant.com> (raw)
In-Reply-To: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com>
References: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com>
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
Attachments:
[text/x-patch] Support-for-OUT-parameters-in-procedures-v2.patch (41.8K, ../4f733cca-5e07-e167-8b38-05b5c9066d04@2ndQuadrant.com/2-Support-for-OUT-parameters-in-procedures-v2.patch)
download | inline diff:diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex de9bacd34f..da77bacf65 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -5875,8 +5875,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<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.sgmlindex 815912666d..309a714fc4 100644--- a/doc/src/sgml/plpgsql.sgml+++ b/doc/src/sgml/plpgsql.sgml@@ -478,6 +478,14 @@ $$ LANGUAGE plpgsql;
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 @@ BEGIN
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 @@ $$ LANGUAGE plpgsql;
<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.sgmlindex c819c7bb4e..38fd60128b 100644--- a/doc/src/sgml/ref/alter_extension.sgml+++ b/doc/src/sgml/ref/alter_extension.sgml@@ -212,11 +212,12 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
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.sgmlindex bcf45c7a85..5c176fb5d8 100644--- a/doc/src/sgml/ref/alter_procedure.sgml+++ b/doc/src/sgml/ref/alter_procedure.sgml@@ -81,8 +81,9 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="para
<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.sgmlindex 6e8ced3eaf..eda91b4e24 100644--- a/doc/src/sgml/ref/comment.sgml+++ b/doc/src/sgml/ref/comment.sgml@@ -178,11 +178,12 @@ COMMENT ON
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.sgmlindex d225695626..2dd6ec7298 100644--- a/doc/src/sgml/ref/create_procedure.sgml+++ b/doc/src/sgml/ref/create_procedure.sgml@@ -97,11 +97,9 @@ CREATE [ OR REPLACE ] PROCEDURE
<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.sgmlindex 6da266ae2d..bf2c6ce1aa 100644--- a/doc/src/sgml/ref/drop_procedure.sgml+++ b/doc/src/sgml/ref/drop_procedure.sgml@@ -67,8 +67,9 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
<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.sgmlindex e9688cce21..9b87bcd519 100644--- a/doc/src/sgml/ref/security_label.sgml+++ b/doc/src/sgml/ref/security_label.sgml@@ -127,11 +127,12 @@ SECURITY LABEL [ FOR <replaceable class="parameter">provider</replaceable> ] ON
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.sgmlindex 0f60a4a0ab..90e4b0f2ff 100644--- a/doc/src/sgml/xfunc.sgml+++ b/doc/src/sgml/xfunc.sgml@@ -179,6 +179,24 @@ SELECT clean_emp();
</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
@@ -716,6 +734,47 @@ DROP FUNCTION sum_n_product (int, int);
</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.cindex e236581a8e..3db1dbb08e 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)
{
@@ -2067,6 +2064,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;
@@ -2127,6 +2127,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() */
@@ -2156,16 +2158,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.yindex 17653ef3a7..0d101d8171 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
@@ -4623,7 +4623,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;
@@ -4632,7 +4632,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;
@@ -6365,7 +6365,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;
@@ -6373,7 +6373,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;
@@ -6519,7 +6519,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);
@@ -6880,7 +6880,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;
@@ -6888,7 +6888,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;
@@ -7409,20 +7409,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)));
@@ -7446,6 +7459,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.
@@ -7824,7 +7855,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;
@@ -7832,7 +7863,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;
@@ -7888,7 +7919,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;
@@ -7898,7 +7929,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;
@@ -7908,7 +7939,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;
@@ -7918,7 +7949,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;
@@ -8393,7 +8424,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;
@@ -8411,7 +8442,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;
@@ -8822,7 +8853,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;
@@ -8831,7 +8862,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;
@@ -8962,7 +8993,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;
@@ -8971,7 +9002,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;
@@ -9273,7 +9304,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;
@@ -9281,7 +9312,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;
@@ -16218,13 +16249,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;
@@ -16233,7 +16265,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;
@@ -16246,7 +16278,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.hindex 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.outindex 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.sqlindex 2cf5461fef..bbea85fc9f 100644--- a/src/pl/plperl/sql/plperl_call.sql+++ b/src/pl/plperl/sql/plperl_call.sql@@ -51,6 +51,26 @@ $$;
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.outindex 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.cindex 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.sqlindex 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 @@ END
$$;
+-- 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.outindex 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.cindex 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.sqlindex 2f792f92bd..46e89b1a9e 100644--- a/src/pl/plpython/sql/plpython_call.sql+++ b/src/pl/plpython/sql/plpython_call.sql@@ -54,6 +54,25 @@ $$;
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.outindex 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.sqlindex 95791d08be..963277e1fb 100644--- a/src/pl/tcl/sql/pltcl_call.sql+++ b/src/pl/tcl/sql/pltcl_call.sql@@ -52,6 +52,25 @@ $$;
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/modules/Makefile b/src/test/modules/Makefileindex a6d2ffbf9e..287214c544 100644--- a/src/test/modules/Makefile+++ b/src/test/modules/Makefile@@ -17,6 +17,7 @@ SUBDIRS = \
test_extensions \
test_ginpostinglist \
test_integerset \
+ test_libpq \
test_misc \
test_parser \
test_pg_dump \
diff --git a/src/test/modules/test_libpq/Makefile b/src/test/modules/test_libpq/Makefilenew file mode 100644index 0000000000..ade53e2ad3--- /dev/null+++ b/src/test/modules/test_libpq/Makefile@@ -0,0 +1,26 @@+#+# Makefile for libpq tests+#++subdir = src/test/modules/test_libpq+top_builddir = ../../../..+include $(top_builddir)/src/Makefile.global++TAP_TESTS = 1++ifeq ($(PORTNAME), win32)+LDFLAGS += -lws2_32+endif++override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS)+LDFLAGS_INTERNAL += $(libpq_pgport)++PROGS = test_stored_proc_outparams$(X)++all: $(PROGS)++check: $(PROGS)+ $(prove_check)++clean distclean maintainer-clean:+ rm -f $(PROGS) *.odiff --git a/src/test/modules/test_libpq/README b/src/test/modules/test_libpq/READMEnew file mode 100644index 0000000000..468ab172a0--- /dev/null+++ b/src/test/modules/test_libpq/README@@ -0,0 +1,4 @@+src/test/modules/test_libpq++This directory is for test programs using libpq, not via psql, and their+associated TAP tests.diff --git a/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.pl b/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.plnew file mode 100644index 0000000000..5441084f40--- /dev/null+++ b/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.pl@@ -0,0 +1,105 @@+use strict;+use warnings;++use PostgresNode;+use TestLib;+use Test::More;++my $node = get_new_node('main');+$node->init;+$node->start;++my @creation = (+ q{+ create procedure out_params_d(arg int, out textres text, out intres int)+ language plpgsql as+ $$+ begin+ textres := 'foo';+ intres := arg * 5;+ end;+ $$},+ q{+ create procedure out_params_d(arg int, out boolres boolean,+ out floatres float8)+ language plpgsql as+ $$+ begin+ boolres := false;+ floatres := arg * 8.0;+ end;+ $$},++ q{+ create procedure out_params_uniq(arg int, out bres boolean,+ out fres float8)+ language plpgsql as+ $$+ begin+ bres := true;+ fres := arg * 13.0;+ end;+ $$},);++++$node->safe_psql('postgres', $_) foreach (@creation);++my $connstr = $node->connstr('postgres');++# query, test name, status, match pattern+my @tests = (+ [+ 'call out_params_uniq($1,$2,$3)',+ 'unique - out param casts not needed',+ 'ok',+ qr{bres\|fres.*t\s*\|\s*26}s+ ],+ [+ 'call out_params_d($1,$2::text,$3)',+ 'duplicate - text param',+ 'ok',+ qr{textres\|intres.*foo\s*\|\s*10}s+ ],+ [+ 'call out_params_d($1,$2::boolean,$3)',+ 'duplicate - boolean param',+ 'ok',+ qr{boolres\|floatres.*f\s*\|\s*16}s+ ],+ [+ 'call no_such_proc($1,$2,$3)',+ 'no such proc',+ 'bad',+ qr{ERROR:\s+procedure no_such_proc\(integer, unknown, unknown\) does not exist}+ ],+ [+ 'call out_params_d($1,$2,$3)',+ 'duplicate - not enough casts',+ 'bad',+ qr{ERROR:\s+procedure out_params_d\(integer, unknown, unknown\) is not unique}+ ],);++# TAP tests are run from the source direectory, but the binary is found in+# the build directory. This is the simplest way to get at it++my $exe = "$ENV{TESTDIR}/test_stored_proc_outparams";++foreach my $test (@tests)+{+ my ($query, $name, $type, $match) = @$test;+ if ($type eq 'ok')+ {+ TestLib::command_like([ $exe, $connstr, $query ], $match, $name);+ }+ else+ {+ TestLib::command_fails_like([ $exe, $connstr, $query ], $match,+ $name);+ }+}++done_testing();++$node->stop('fast');+diff --git a/src/test/modules/test_libpq/test_stored_proc_outparams.c b/src/test/modules/test_libpq/test_stored_proc_outparams.cnew file mode 100644index 0000000000..441ce7ef86--- /dev/null+++ b/src/test/modules/test_libpq/test_stored_proc_outparams.c@@ -0,0 +1,89 @@+/*+ * src/test/examples/testlibpq.c+ *+ *+ * testlibpq.c+ *+ * Test the C version of libpq, the PostgreSQL frontend library.+ */+#include <stdio.h>+#include <stdlib.h>+#include "libpq-fe.h"++static void test_sp_out(PGconn *conn, const char *query);++static void+exit_nicely(PGconn *conn)+{+ PQfinish(conn);+ exit(1);+}++int+main(int argc, char **argv)+{+ const char *conninfo;+ const char *query;+ PGconn *conn;++ /* required args: conninfo and query string */+ if (argc != 3)+ {+ fprintf(stderr, "usage: %s conninfo querystring\n", argv[0]);+ exit(2);+ }+ conninfo = argv[1];+ query = argv[2];++ /* Make a connection to the database */+ conn = PQconnectdb(conninfo);++ /* Check to see that the backend connection was successfully made */+ if (PQstatus(conn) != CONNECTION_OK)+ {+ fprintf(stderr, "Connection to database failed: %s",+ PQerrorMessage(conn));+ exit_nicely(conn);+ }++ test_sp_out(conn, query);++ /* close the connection to the database and cleanup */+ PQfinish(conn);++ return 0;+}++#define INTOID 23+#define UNKNOWNOID 705++void+test_sp_out(PGconn *conn, const char *query)+{+ /*+ * We expect a call to a procedure with one IN param and 2 OUT params. The+ * query needs to match this pattern for success. Casts are needed in the+ * query (only) to disambiguate. The IN param here has a hardcoded value+ * of 2.+ */++ PGresult *res;+ const PQprintOpt opt = {1, 1, 0, 0, 0, 0, "|", NULL, NULL, NULL};+ int nParams = 3;+ Oid paramTypes[3] = {INTOID, UNKNOWNOID, UNKNOWNOID};+ const char *paramValues[3] = {"2", NULL, NULL};+ int resultFormat = 0;++ res = PQexecParams(conn, query, nParams, paramTypes, paramValues,+ NULL, NULL, resultFormat);++ if (PQresultStatus(res) != PGRES_TUPLES_OK)+ {+ fprintf(stderr, "CALL failed: %s", PQerrorMessage(conn));+ PQclear(res);+ exit_nicely(conn);+ }++ PQprint(stdout, res, &opt);++}diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.outindex 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.sqlindex 89b96d580f..2ef1c82cea 100644--- a/src/test/regress/sql/create_procedure.sql+++ b/src/test/regress/sql/create_procedure.sql@@ -112,6 +112,18 @@ $$;
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 @@ CALL sum(1); -- error: not a procedure
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-jdbc@postgresql.org
Cc: andrew.dunstan@2ndquadrant.com, peter.eisentraut@2ndquadrant.com, pgsql-hackers@postgresql.org
Subject: Re: Support for OUT parameters in procedures
In-Reply-To: <4f733cca-5e07-e167-8b38-05b5c9066d04@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox