Skip to content

Commit 33803f6

Browse files
peterepetere
committed
Support INOUT arguments in procedures
In a top-level CALL, the values of INOUT arguments will be returned as a result row. In PL/pgSQL, the values are assigned back to the input arguments. In other languages, the same convention as for return a record from a function is used. That does not require any code changes in the PL implementations. Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
1 parent 484a4a0 commit 33803f6

32 files changed

+792
-50
lines changed

‎doc/src/sgml/plperl.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,20 @@ SELECT * FROM perl_row();
278278
hash will be returned as null values.
279279
</para>
280280

281+
<para>
282+
Similarly, output arguments of procedures can be returned as a hash
283+
reference:
284+
285+
<programlisting>
286+
CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
287+
my ($a, $b) = @_;
288+
return {a =&gt; $a * 3, b =&gt; $b * 3};
289+
$$ LANGUAGE plperl;
290+
291+
CALL perl_triple(5, 10);
292+
</programlisting>
293+
</para>
294+
281295
<para>
282296
PL/Perl functions can also return sets of either scalar or
283297
composite types. Usually you'll want to return rows one at a

‎doc/src/sgml/plpgsql.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1870,6 +1870,22 @@ SELECT * FROM get_available_flightid(CURRENT_DATE);
18701870
then <symbol>NULL</symbol> must be returned. Returning any other value
18711871
will result in an error.
18721872
</para>
1873+
1874+
<para>
1875+
If a procedure has output parameters, then the output values can be
1876+
assigned to the parameters as if they were variables. For example:
1877+
<programlisting>
1878+
CREATE PROCEDURE triple(INOUT x int)
1879+
LANGUAGE plpgsql
1880+
AS $$
1881+
BEGIN
1882+
x := x * 3;
1883+
END;
1884+
$$;
1885+
1886+
CALL triple(5);
1887+
</programlisting>
1888+
</para>
18731889
</sect2>
18741890

18751891
<sect2 id="plpgsql-conditionals">

‎doc/src/sgml/plpython.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -649,6 +649,17 @@ return (1, 2)
649649
$$ LANGUAGE plpythonu;
650650

651651
SELECT * FROM multiout_simple();
652+
</programlisting>
653+
</para>
654+
655+
<para>
656+
Output parameters of procedures are passed back the same way. For example:
657+
<programlisting>
658+
CREATE PROCEDURE python_triple(INOUT a integer, INOUT b integer) AS $$
659+
return (a * 3, b * 3)
660+
$$ LANGUAGE plpythonu;
661+
662+
CALL python_triple(5, 10);
652663
</programlisting>
653664
</para>
654665
</sect2>

‎doc/src/sgml/pltcl.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -186,6 +186,18 @@ $$ LANGUAGE pltcl;
186186
</programlisting>
187187
</para>
188188

189+
<para>
190+
Output arguments of procedures are returned in the same way, for example:
191+
192+
<programlisting>
193+
CREATE PROCEDURE tcl_triple(INOUT a integer, INOUT b integer) AS $$
194+
return [list a [expr {$1 * 3}] b [expr {$2 * 3}]]
195+
$$ LANGUAGE pltcl;
196+
197+
CALL tcl_triple(5, 10);
198+
</programlisting>
199+
</para>
200+
189201
<tip>
190202
<para>
191203
The result list can be made from an array representation of the

‎doc/src/sgml/ref/call.sgml

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,10 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
3131
<para>
3232
<command>CALL</command> executes a procedure.
3333
</para>
34+
35+
<para>
36+
If the procedure has output arguments, then a result row will be returned.
37+
</para>
3438
</refsect1>
3539

3640
<refsect1>

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

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -96,8 +96,11 @@ CREATE [ OR REPLACE ] PROCEDURE
9696

9797
<listitem>
9898
<para>
99-
The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
100-
If omitted, the default is <literal>IN</literal>.
99+
The mode of an argument: <literal>IN</literal>,
100+
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
101+
the default is <literal>IN</literal>. (<literal>OUT</literal>
102+
arguments are currently not supported for procedures. Use
103+
<literal>INOUT</literal> instead.)
101104
</para>
102105
</listitem>
103106
</varlistentry>

‎src/backend/catalog/pg_proc.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -438,7 +438,8 @@ ProcedureCreate(const char *procedureName,
438438
TupleDesc newdesc;
439439

440440
olddesc = build_function_result_tupdesc_t(oldtup);
441-
newdesc = build_function_result_tupdesc_d(allParameterTypes,
441+
newdesc = build_function_result_tupdesc_d(prokind,
442+
allParameterTypes,
442443
parameterModes,
443444
parameterNames);
444445
if (olddesc == NULL && newdesc == NULL)
@@ -925,6 +926,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
925926
querytree_sublist);
926927
}
927928

929+
check_sql_fn_statements(querytree_list);
928930
(void) check_sql_fn_retval(funcoid, proc->prorettype,
929931
querytree_list,
930932
NULL, NULL);

‎src/backend/commands/functioncmds.c

Lines changed: 55 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@
6868
#include "utils/memutils.h"
6969
#include "utils/rel.h"
7070
#include "utils/syscache.h"
71+
#include "utils/typcache.h"
7172
#include "utils/tqual.h"
7273

7374
/*
@@ -281,10 +282,11 @@ interpret_function_parameter_list(ParseState *pstate,
281282

282283
if (objtype == OBJECT_PROCEDURE)
283284
{
284-
if (fp->mode == FUNC_PARAM_OUT || fp->mode == FUNC_PARAM_INOUT)
285+
if (fp->mode == FUNC_PARAM_OUT)
285286
ereport(ERROR,
286287
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
287-
(errmsg("procedures cannot have OUT parameters"))));
288+
(errmsg("procedures cannot have OUT arguments"),
289+
errhint("INOUT arguments are permitted."))));
288290
}
289291

290292
/* handle input parameters */
@@ -302,7 +304,9 @@ interpret_function_parameter_list(ParseState *pstate,
302304
/* handle output parameters */
303305
if (fp->mode != FUNC_PARAM_IN && fp->mode != FUNC_PARAM_VARIADIC)
304306
{
305-
if (outCount == 0) /* save first output param's type */
307+
if (objtype == OBJECT_PROCEDURE)
308+
*requiredResultType = RECORDOID;
309+
else if (outCount == 0) /* save first output param's type */
306310
*requiredResultType = toid;
307311
outCount++;
308312
}
@@ -1003,12 +1007,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
10031007

10041008
if (stmt->is_procedure)
10051009
{
1006-
/*
1007-
* Sometime in the future, procedures might be allowed to return
1008-
* results; for now, they all return VOID.
1009-
*/
10101010
Assert(!stmt->returnType);
1011-
prorettype = VOIDOID;
1011+
prorettype = requiredResultType ? requiredResultType : VOIDOID;
10121012
returnsSet = false;
10131013
}
10141014
else if (stmt->returnType)
@@ -2206,7 +2206,7 @@ ExecuteDoStmt(DoStmt *stmt, bool atomic)
22062206
* commits that might occur inside the procedure.
22072207
*/
22082208
void
2209-
ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic)
2209+
ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest)
22102210
{
22112211
ListCell *lc;
22122212
FuncExpr *fexpr;
@@ -2219,6 +2219,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic)
22192219
EState *estate;
22202220
ExprContext *econtext;
22212221
HeapTuple tp;
2222+
Datum retval;
22222223

22232224
fexpr = stmt->funcexpr;
22242225
Assert(fexpr);
@@ -2285,7 +2286,51 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic)
22852286
i++;
22862287
}
22872288

2288-
FunctionCallInvoke(&fcinfo);
2289+
retval = FunctionCallInvoke(&fcinfo);
2290+
2291+
if (fexpr->funcresulttype == VOIDOID)
2292+
{
2293+
/* do nothing */
2294+
}
2295+
else if (fexpr->funcresulttype == RECORDOID)
2296+
{
2297+
/*
2298+
* send tuple to client
2299+
*/
2300+
2301+
HeapTupleHeader td;
2302+
Oid tupType;
2303+
int32 tupTypmod;
2304+
TupleDesc retdesc;
2305+
HeapTupleData rettupdata;
2306+
TupOutputState *tstate;
2307+
TupleTableSlot *slot;
2308+
2309+
if (fcinfo.isnull)
2310+
elog(ERROR, "procedure returned null record");
2311+
2312+
td = DatumGetHeapTupleHeader(retval);
2313+
tupType = HeapTupleHeaderGetTypeId(td);
2314+
tupTypmod = HeapTupleHeaderGetTypMod(td);
2315+
retdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
2316+
2317+
tstate = begin_tup_output_tupdesc(dest, retdesc);
2318+
2319+
rettupdata.t_len = HeapTupleHeaderGetDatumLength(td);
2320+
ItemPointerSetInvalid(&(rettupdata.t_self));
2321+
rettupdata.t_tableOid = InvalidOid;
2322+
rettupdata.t_data = td;
2323+
2324+
slot = ExecStoreTuple(&rettupdata, tstate->slot, InvalidBuffer, false);
2325+
tstate->dest->receiveSlot(slot, tstate->dest);
2326+
2327+
end_tup_output(tstate);
2328+
2329+
ReleaseTupleDesc(retdesc);
2330+
}
2331+
else
2332+
elog(ERROR, "unexpected result type for procedure: %u",
2333+
fexpr->funcresulttype);
22892334

22902335
FreeExecutorState(estate);
22912336
}

‎src/backend/executor/functions.c

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -721,6 +721,8 @@ init_sql_fcache(FmgrInfo *finfo, Oid collation, bool lazyEvalOK)
721721
list_copy(queryTree_sublist));
722722
}
723723

724+
check_sql_fn_statements(flat_query_list);
725+
724726
/*
725727
* Check that the function returns the type it claims to. Although in
726728
* simple cases this was already done when the function was defined, we
@@ -1486,6 +1488,55 @@ ShutdownSQLFunction(Datum arg)
14861488
fcache->shutdown_reg = false;
14871489
}
14881490

1491+
/*
1492+
* check_sql_fn_statements
1493+
*
1494+
* Check statements in an SQL function. Error out if there is anything that
1495+
* is not acceptable.
1496+
*/
1497+
void
1498+
check_sql_fn_statements(List *queryTreeList)
1499+
{
1500+
ListCell *lc;
1501+
1502+
foreach(lc, queryTreeList)
1503+
{
1504+
Query *query = lfirst_node(Query, lc);
1505+
1506+
/*
1507+
* Disallow procedures with output arguments. The current
1508+
* implementation would just throw the output values away, unless the
1509+
* statement is the last one. Per SQL standard, we should assign the
1510+
* output values by name. By disallowing this here, we preserve an
1511+
* opportunity for future improvement.
1512+
*/
1513+
if (query->commandType == CMD_UTILITY &&
1514+
IsA(query->utilityStmt, CallStmt))
1515+
{
1516+
CallStmt *stmt = castNode(CallStmt, query->utilityStmt);
1517+
HeapTuple tuple;
1518+
int numargs;
1519+
Oid *argtypes;
1520+
char **argnames;
1521+
char *argmodes;
1522+
int i;
1523+
1524+
tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(stmt->funcexpr->funcid));
1525+
if (!HeapTupleIsValid(tuple))
1526+
elog(ERROR, "cache lookup failed for function %u", stmt->funcexpr->funcid);
1527+
numargs = get_func_arg_info(tuple, &argtypes, &argnames, &argmodes);
1528+
ReleaseSysCache(tuple);
1529+
1530+
for (i = 0; i < numargs; i++)
1531+
{
1532+
if (argmodes && (argmodes[i] == PROARGMODE_INOUT || argmodes[i] == PROARGMODE_OUT))
1533+
ereport(ERROR,
1534+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1535+
errmsg("calling procedures with output arguments is not supported in SQL functions")));
1536+
}
1537+
}
1538+
}
1539+
}
14891540

14901541
/*
14911542
* check_sql_fn_retval() -- check return value of a list of sql parse trees.

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -661,7 +661,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
661661

662662
case T_CallStmt:
663663
ExecuteCallStmt(castNode(CallStmt, parsetree), params,
664-
(context != PROCESS_UTILITY_TOPLEVEL || IsTransactionBlock()));
664+
(context != PROCESS_UTILITY_TOPLEVEL || IsTransactionBlock()),
665+
dest);
665666
break;
666667

667668
case T_ClusterStmt:

‎src/backend/utils/fmgr/funcapi.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1205,7 +1205,8 @@ build_function_result_tupdesc_t(HeapTuple procTuple)
12051205
if (isnull)
12061206
proargnames = PointerGetDatum(NULL); /* just to be sure */
12071207

1208-
return build_function_result_tupdesc_d(proallargtypes,
1208+
return build_function_result_tupdesc_d(procform->prokind,
1209+
proallargtypes,
12091210
proargmodes,
12101211
proargnames);
12111212
}
@@ -1218,10 +1219,12 @@ build_function_result_tupdesc_t(HeapTuple procTuple)
12181219
* convenience of ProcedureCreate, which needs to be able to compute the
12191220
* tupledesc before actually creating the function.
12201221
*
1221-
* Returns NULL if there are not at least two OUT or INOUT arguments.
1222+
* For functions (but not for procedures), returns NULL if there are not at
1223+
* least two OUT or INOUT arguments.
12221224
*/
12231225
TupleDesc
1224-
build_function_result_tupdesc_d(Datum proallargtypes,
1226+
build_function_result_tupdesc_d(char prokind,
1227+
Datum proallargtypes,
12251228
Datum proargmodes,
12261229
Datum proargnames)
12271230
{
@@ -1311,7 +1314,7 @@ build_function_result_tupdesc_d(Datum proallargtypes,
13111314
* If there is no output argument, or only one, the function does not
13121315
* return tuples.
13131316
*/
1314-
if (numoutargs < 2)
1317+
if (numoutargs < 2 && prokind != PROKIND_PROCEDURE)
13151318
return NULL;
13161319

13171320
desc = CreateTemplateTupleDesc(numoutargs, false);

‎src/include/commands/defrem.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717
#include "catalog/objectaddress.h"
1818
#include "nodes/params.h"
1919
#include "nodes/parsenodes.h"
20+
#include "tcop/dest.h"
2021
#include "utils/array.h"
2122

2223
/* commands/dropcmds.c */
@@ -62,7 +63,7 @@ extern void DropTransformById(Oid transformOid);
6263
extern void IsThereFunctionInNamespace(const char *proname, int pronargs,
6364
oidvector *proargtypes, Oid nspOid);
6465
extern void ExecuteDoStmt(DoStmt *stmt, bool atomic);
65-
extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic);
66+
extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest);
6667
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok);
6768
extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok);
6869
extern void interpret_function_parameter_list(ParseState *pstate,

‎src/include/executor/functions.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,8 @@ extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple procedureTupl
2929
extern void sql_fn_parser_setup(struct ParseState *pstate,
3030
SQLFunctionParseInfoPtr pinfo);
3131

32+
extern void check_sql_fn_statements(List *queryTreeList);
33+
3234
extern bool check_sql_fn_retval(Oid func_id, Oid rettype,
3335
List *queryTreeList,
3436
bool *modifyTargetList,

‎src/include/funcapi.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -187,7 +187,8 @@ extern int get_func_input_arg_names(Datum proargnames, Datum proargmodes,
187187
extern int get_func_trftypes(HeapTuple procTup, Oid **p_trftypes);
188188
extern char *get_func_result_name(Oid functionId);
189189

190-
extern TupleDesc build_function_result_tupdesc_d(Datum proallargtypes,
190+
extern TupleDesc build_function_result_tupdesc_d(char prokind,
191+
Datum proallargtypes,
191192
Datum proargmodes,
192193
Datum proargnames);
193194
extern TupleDesc build_function_result_tupdesc_t(HeapTuple procTuple);

0 commit comments

Comments
 (0)