Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit33803f6

Browse files
committed
Support INOUT arguments in procedures
In a top-level CALL, the values of INOUT arguments will be returned as aresult row. In PL/pgSQL, the values are assigned back to the inputarguments. In other languages, the same convention as for return arecord from a function is used. That does not require any code changesin the PL implementations.Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
1 parent484a4a0 commit33803f6

File tree

32 files changed

+792
-50
lines changed

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
TupleDescnewdesc;
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+
elseif (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
elseif (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,ParamListInfoparams,boolatomic)
2209+
ExecuteCallStmt(CallStmt*stmt,ParamListInfoparams,boolatomic,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
HeapTupletp;
2222+
Datumretval;
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+
elseif (fexpr->funcresulttype==RECORDOID)
2296+
{
2297+
/*
2298+
* send tuple to client
2299+
*/
2300+
2301+
HeapTupleHeadertd;
2302+
OidtupType;
2303+
int32tupTypmod;
2304+
TupleDescretdesc;
2305+
HeapTupleDatarettupdata;
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+
HeapTupletuple;
1518+
intnumargs;
1519+
Oid*argtypes;
1520+
char**argnames;
1521+
char*argmodes;
1522+
inti;
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
caseT_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
caseT_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-
returnbuild_function_result_tupdesc_d(proallargtypes,
1208+
returnbuild_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(Datumproallargtypes,
1226+
build_function_result_tupdesc_d(charprokind,
1227+
Datumproallargtypes,
12251228
Datumproargmodes,
12261229
Datumproargnames)
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
returnNULL;
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
externvoidIsThereFunctionInNamespace(constchar*proname,intpronargs,
6364
oidvector*proargtypes,OidnspOid);
6465
externvoidExecuteDoStmt(DoStmt*stmt,boolatomic);
65-
externvoidExecuteCallStmt(CallStmt*stmt,ParamListInfoparams,boolatomic);
66+
externvoidExecuteCallStmt(CallStmt*stmt,ParamListInfoparams,boolatomic,DestReceiver*dest);
6667
externOidget_cast_oid(Oidsourcetypeid,Oidtargettypeid,boolmissing_ok);
6768
externOidget_transform_oid(Oidtype_id,Oidlang_id,boolmissing_ok);
6869
externvoidinterpret_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
externvoidsql_fn_parser_setup(structParseState*pstate,
3030
SQLFunctionParseInfoPtrpinfo);
3131

32+
externvoidcheck_sql_fn_statements(List*queryTreeList);
33+
3234
externboolcheck_sql_fn_retval(Oidfunc_id,Oidrettype,
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
externintget_func_trftypes(HeapTupleprocTup,Oid**p_trftypes);
188188
externchar*get_func_result_name(OidfunctionId);
189189

190-
externTupleDescbuild_function_result_tupdesc_d(Datumproallargtypes,
190+
externTupleDescbuild_function_result_tupdesc_d(charprokind,
191+
Datumproallargtypes,
191192
Datumproargmodes,
192193
Datumproargnames);
193194
externTupleDescbuild_function_result_tupdesc_t(HeapTupleprocTuple);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp