Skip to content

Commit 2453ea1

Browse files
peterepetere
committed
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. Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com
1 parent e899742 commit 2453ea1

28 files changed

+416
-93
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5875,8 +5875,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
58755875
<para>
58765876
An array with the data types of the function arguments. This includes
58775877
only input arguments (including <literal>INOUT</literal> and
5878-
<literal>VARIADIC</literal> arguments), and thus represents
5879-
the call signature of the function.
5878+
<literal>VARIADIC</literal> arguments), as well as
5879+
<literal>OUT</literal> parameters of procedures, and thus represents
5880+
the call signature of the function or procedure.
58805881
</para></entry>
58815882
</row>
58825883

‎doc/src/sgml/plpgsql.sgml

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,14 @@ $$ LANGUAGE plpgsql;
478478
included it, but it would be redundant.
479479
</para>
480480

481+
<para>
482+
To call a function with <literal>OUT</literal> parameters, omit the
483+
output parameter in the function call:
484+
<programlisting>
485+
SELECT sales_tax(100.00);
486+
</programlisting>
487+
</para>
488+
481489
<para>
482490
Output parameters are most useful when returning multiple values.
483491
A trivial example is:
@@ -489,6 +497,11 @@ BEGIN
489497
prod := x * y;
490498
END;
491499
$$ LANGUAGE plpgsql;
500+
501+
SELECT * FROM sum_n_product(2, 4);
502+
sum | prod
503+
-----+------
504+
6 | 8
492505
</programlisting>
493506

494507
As discussed in <xref linkend="xfunc-output-parameters"/>, this
@@ -497,6 +510,31 @@ $$ LANGUAGE plpgsql;
497510
<literal>RETURNS record</literal>.
498511
</para>
499512

513+
<para>
514+
This also works with procedures, for example:
515+
516+
<programlisting>
517+
CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
518+
BEGIN
519+
sum := x + y;
520+
prod := x * y;
521+
END;
522+
$$ LANGUAGE plpgsql;
523+
</programlisting>
524+
525+
In a call to a procedure, all the parameters must be specified. For
526+
output parameters, <literal>NULL</literal> may be specified.
527+
<programlisting>
528+
CALL sum_n_product(2, 4, NULL, NULL);
529+
sum | prod
530+
-----+------
531+
6 | 8
532+
</programlisting>
533+
Output parameters in procedures become more interesting in nested calls,
534+
where they can be assigned to variables. See <xref
535+
linkend="plpgsql-statements-calling-procedure"/> for details.
536+
</para>
537+
500538
<para>
501539
Another way to declare a <application>PL/pgSQL</application> function
502540
is with <literal>RETURNS TABLE</literal>, for example:

‎doc/src/sgml/ref/alter_extension.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -212,11 +212,12 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
212212
argument: <literal>IN</literal>, <literal>OUT</literal>,
213213
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
214214
If omitted, the default is <literal>IN</literal>.
215-
Note that <command>ALTER EXTENSION</command> does not actually pay
216-
any attention to <literal>OUT</literal> arguments, since only the input
217-
arguments are needed to determine the function's identity.
218-
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
219-
and <literal>VARIADIC</literal> arguments.
215+
Note that <command>ALTER EXTENSION</command> does not actually pay any
216+
attention to <literal>OUT</literal> arguments for functions and
217+
aggregates (but not procedures), since only the input arguments are
218+
needed to determine the function's identity. So it is sufficient to
219+
list the <literal>IN</literal>, <literal>INOUT</literal>, and
220+
<literal>VARIADIC</literal> arguments for functions and aggregates.
220221
</para>
221222
</listitem>
222223
</varlistentry>

‎doc/src/sgml/ref/alter_procedure.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -81,8 +81,9 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="para
8181

8282
<listitem>
8383
<para>
84-
The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
85-
If omitted, the default is <literal>IN</literal>.
84+
The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
85+
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
86+
the default is <literal>IN</literal>.
8687
</para>
8788
</listitem>
8889
</varlistentry>

‎doc/src/sgml/ref/comment.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -178,11 +178,12 @@ COMMENT ON
178178
argument: <literal>IN</literal>, <literal>OUT</literal>,
179179
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
180180
If omitted, the default is <literal>IN</literal>.
181-
Note that <command>COMMENT</command> does not actually pay
182-
any attention to <literal>OUT</literal> arguments, since only the input
183-
arguments are needed to determine the function's identity.
184-
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
185-
and <literal>VARIADIC</literal> arguments.
181+
Note that <command>COMMENT</command> does not actually pay any attention
182+
to <literal>OUT</literal> arguments for functions and aggregates (but
183+
not procedures), since only the input arguments are needed to determine
184+
the function's identity. So it is sufficient to list the
185+
<literal>IN</literal>, <literal>INOUT</literal>, and
186+
<literal>VARIADIC</literal> arguments for functions and aggregates.
186187
</para>
187188
</listitem>
188189
</varlistentry>

‎doc/src/sgml/ref/create_procedure.sgml

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -97,11 +97,9 @@ CREATE [ OR REPLACE ] PROCEDURE
9797

9898
<listitem>
9999
<para>
100-
The mode of an argument: <literal>IN</literal>,
100+
The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
101101
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
102-
the default is <literal>IN</literal>. (<literal>OUT</literal>
103-
arguments are currently not supported for procedures. Use
104-
<literal>INOUT</literal> instead.)
102+
the default is <literal>IN</literal>.
105103
</para>
106104
</listitem>
107105
</varlistentry>

‎doc/src/sgml/ref/drop_procedure.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -67,8 +67,9 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
6767

6868
<listitem>
6969
<para>
70-
The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
71-
If omitted, the default is <literal>IN</literal>.
70+
The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
71+
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
72+
the default is <literal>IN</literal>.
7273
</para>
7374
</listitem>
7475
</varlistentry>

‎doc/src/sgml/ref/security_label.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -127,11 +127,12 @@ SECURITY LABEL [ FOR <replaceable class="parameter">provider</replaceable> ] ON
127127
argument: <literal>IN</literal>, <literal>OUT</literal>,
128128
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
129129
If omitted, the default is <literal>IN</literal>.
130-
Note that <command>SECURITY LABEL</command> does not actually
131-
pay any attention to <literal>OUT</literal> arguments, since only the input
132-
arguments are needed to determine the function's identity.
133-
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
134-
and <literal>VARIADIC</literal> arguments.
130+
Note that <command>SECURITY LABEL</command> does not actually pay any
131+
attention to <literal>OUT</literal> arguments for functions and
132+
aggregates (but not procedures), since only the input arguments are
133+
needed to determine the function's identity. So it is sufficient to
134+
list the <literal>IN</literal>, <literal>INOUT</literal>, and
135+
<literal>VARIADIC</literal> arguments for functions and aggregates.
135136
</para>
136137
</listitem>
137138
</varlistentry>

‎doc/src/sgml/xfunc.sgml

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,24 @@ SELECT clean_emp();
179179
</screen>
180180
</para>
181181

182+
<para>
183+
You can also write this as a procedure, thus avoiding the issue of the
184+
return type. For example:
185+
<screen>
186+
CREATE PROCEDURE clean_emp() AS '
187+
DELETE FROM emp
188+
WHERE salary &lt; 0;
189+
' LANGUAGE SQL;
190+
191+
CALL clean_emp();
192+
</screen>
193+
In simple cases like this, the difference between a function returning
194+
<type>void</type> and a procedure is mostly stylistic. However,
195+
procedures offer additional functionality such as transaction control
196+
that is not available in functions. Also, procedures are SQL standard
197+
whereas returning <type>void</type> is a PostgreSQL extension.
198+
</para>
199+
182200
<note>
183201
<para>
184202
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);
716734
</para>
717735
</sect2>
718736

737+
<sect2 id="xfunc-output-parameters-proc">
738+
<title><acronym>SQL</acronym> Procedures with Output Parameters</title>
739+
740+
<indexterm>
741+
<primary>procedures</primary>
742+
<secondary>output parameter</secondary>
743+
</indexterm>
744+
745+
<para>
746+
Output parameters are also supported in procedures, but they work a bit
747+
differently from functions. Notably, output parameters
748+
<emphasis>are</emphasis> included in the signature of a procedure and
749+
must be specified in the procedure call.
750+
</para>
751+
752+
<para>
753+
For example, the bank account debiting routine from earlier could be
754+
written like this:
755+
<programlisting>
756+
CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
757+
UPDATE bank
758+
SET balance = balance - debit
759+
WHERE accountno = tp1.accountno
760+
RETURNING balance;
761+
$$ LANGUAGE SQL;
762+
</programlisting>
763+
To call this procedure, it is irrelevant what is passed as the argument
764+
of the <literal>OUT</literal> parameter, so you could pass
765+
<literal>NULL</literal>:
766+
<programlisting>
767+
CALL tp1(17, 100.0, NULL);
768+
</programlisting>
769+
</para>
770+
771+
<para>
772+
Procedures with output parameters are more useful in PL/pgSQL, where the
773+
output parameters can be assigned to variables. See <xref
774+
linkend="plpgsql-statements-calling-procedure"/> for details.
775+
</para>
776+
</sect2>
777+
719778
<sect2 id="xfunc-sql-variadic-functions">
720779
<title><acronym>SQL</acronym> Functions with Variable Numbers of Arguments</title>
721780

‎src/backend/catalog/pg_proc.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -249,6 +249,9 @@ ProcedureCreate(const char *procedureName,
249249
elog(ERROR, "variadic parameter must be last");
250250
break;
251251
case PROARGMODE_OUT:
252+
if (OidIsValid(variadicType) && prokind == PROKIND_PROCEDURE)
253+
elog(ERROR, "variadic parameter must be last");
254+
break;
252255
case PROARGMODE_TABLE:
253256
/* okay */
254257
break;
@@ -462,10 +465,12 @@ ProcedureCreate(const char *procedureName,
462465
if (isnull)
463466
proargmodes = PointerGetDatum(NULL); /* just to be sure */
464467

465-
n_old_arg_names = get_func_input_arg_names(proargnames,
468+
n_old_arg_names = get_func_input_arg_names(prokind,
469+
proargnames,
466470
proargmodes,
467471
&old_arg_names);
468-
n_new_arg_names = get_func_input_arg_names(parameterNames,
472+
n_new_arg_names = get_func_input_arg_names(prokind,
473+
parameterNames,
469474
parameterModes,
470475
&new_arg_names);
471476
for (j = 0; j < n_old_arg_names; j++)

‎src/backend/commands/functioncmds.c

Lines changed: 33 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -194,8 +194,8 @@ interpret_function_parameter_list(ParseState *pstate,
194194
Oid *requiredResultType)
195195
{
196196
int parameterCount = list_length(parameters);
197-
Oid *inTypes;
198-
int inCount = 0;
197+
Oid *sigArgTypes;
198+
int sigArgCount = 0;
199199
Datum *allTypes;
200200
Datum *paramModes;
201201
Datum *paramNames;
@@ -209,7 +209,7 @@ interpret_function_parameter_list(ParseState *pstate,
209209
*variadicArgType = InvalidOid; /* default result */
210210
*requiredResultType = InvalidOid; /* default result */
211211

212-
inTypes = (Oid *) palloc(parameterCount * sizeof(Oid));
212+
sigArgTypes = (Oid *) palloc(parameterCount * sizeof(Oid));
213213
allTypes = (Datum *) palloc(parameterCount * sizeof(Datum));
214214
paramModes = (Datum *) palloc(parameterCount * sizeof(Datum));
215215
paramNames = (Datum *) palloc0(parameterCount * sizeof(Datum));
@@ -281,25 +281,21 @@ interpret_function_parameter_list(ParseState *pstate,
281281
errmsg("functions cannot accept set arguments")));
282282
}
283283

284-
if (objtype == OBJECT_PROCEDURE)
285-
{
286-
if (fp->mode == FUNC_PARAM_OUT)
287-
ereport(ERROR,
288-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
289-
errmsg("procedures cannot have OUT arguments"),
290-
errhint("INOUT arguments are permitted.")));
291-
}
292-
293284
/* handle input parameters */
294285
if (fp->mode != FUNC_PARAM_OUT && fp->mode != FUNC_PARAM_TABLE)
286+
isinput = true;
287+
288+
/* handle signature parameters */
289+
if (fp->mode == FUNC_PARAM_IN || fp->mode == FUNC_PARAM_INOUT ||
290+
(objtype == OBJECT_PROCEDURE && fp->mode == FUNC_PARAM_OUT) ||
291+
fp->mode == FUNC_PARAM_VARIADIC)
295292
{
296-
/* other input parameters can't follow a VARIADIC parameter */
293+
/* other signature parameters can't follow a VARIADIC parameter */
297294
if (varCount > 0)
298295
ereport(ERROR,
299296
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
300-
errmsg("VARIADIC parameter must be the last input parameter")));
301-
inTypes[inCount++] = toid;
302-
isinput = true;
297+
errmsg("VARIADIC parameter must be the last signature parameter")));
298+
sigArgTypes[sigArgCount++] = toid;
303299
}
304300

305301
/* handle output parameters */
@@ -429,7 +425,7 @@ interpret_function_parameter_list(ParseState *pstate,
429425
}
430426

431427
/* Now construct the proper outputs as needed */
432-
*parameterTypes = buildoidvector(inTypes, inCount);
428+
*parameterTypes = buildoidvector(sigArgTypes, sigArgCount);
433429

434430
if (outCount > 0 || varCount > 0)
435431
{
@@ -2067,6 +2063,9 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
20672063
int nargs;
20682064
int i;
20692065
AclResult aclresult;
2066+
Oid *argtypes;
2067+
char **argnames;
2068+
char *argmodes;
20702069
FmgrInfo flinfo;
20712070
CallContext *callcontext;
20722071
EState *estate;
@@ -2127,6 +2126,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
21272126
tp);
21282127
nargs = list_length(fexpr->args);
21292128

2129+
get_func_arg_info(tp, &argtypes, &argnames, &argmodes);
2130+
21302131
ReleaseSysCache(tp);
21312132

21322133
/* safety check; see ExecInitFunc() */
@@ -2156,16 +2157,24 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
21562157
i = 0;
21572158
foreach(lc, fexpr->args)
21582159
{
2159-
ExprState *exprstate;
2160-
Datum val;
2161-
bool isnull;
2160+
if (argmodes && argmodes[i] == PROARGMODE_OUT)
2161+
{
2162+
fcinfo->args[i].value = 0;
2163+
fcinfo->args[i].isnull = true;
2164+
}
2165+
else
2166+
{
2167+
ExprState *exprstate;
2168+
Datum val;
2169+
bool isnull;
21622170

2163-
exprstate = ExecPrepareExpr(lfirst(lc), estate);
2171+
exprstate = ExecPrepareExpr(lfirst(lc), estate);
21642172

2165-
val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull);
2173+
val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull);
21662174

2167-
fcinfo->args[i].value = val;
2168-
fcinfo->args[i].isnull = isnull;
2175+
fcinfo->args[i].value = val;
2176+
fcinfo->args[i].isnull = isnull;
2177+
}
21692178

21702179
i++;
21712180
}

‎src/backend/executor/functions.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -259,7 +259,8 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple,
259259
if (isNull)
260260
proargmodes = PointerGetDatum(NULL); /* just to be sure */
261261

262-
n_arg_names = get_func_input_arg_names(proargnames, proargmodes,
262+
n_arg_names = get_func_input_arg_names(procedureStruct->prokind,
263+
proargnames, proargmodes,
263264
&pinfo->argnames);
264265

265266
/* Paranoia: ignore the result if too few array entries */

0 commit comments

Comments
 (0)