This patch set adds support for INOUT parameters to procedures.
Currently, INOUT and OUT parameters are not supported.
A top-level CALL returns the output parameters as a result row. In
PL/pgSQL, I have added special support to pass the output back into the
variables, as one would expect.
These patches apply on top of the "prokind" patch set v2. (Tom has
submitted an updated version of that, which overlaps with some of the
changes I've made here. I will work on consolidating that soon.)
So ... no OUT parameters, though. I'm struggling to find a way to make
this compatible with everything else. For functions, the OUT parameters
don't appear in the signature. But that is not how this is specified in
the SQL standard for procedures (I think). In PL/pgSQL, you'd expect that
CREATE PROCEDURE foo(a int, OUT b int) ...
could be called like
CALL foo(x, y);
but that would require a different way of parsing function invocation.
At the top-level, it's even more dubious. In DB2, apparently you write
CALL foo(123, ?);
with a literal ? for the OUT parameters.
In Oracle, I've seen CALL ... INTO syntax.
Anyway, I'm leaving this out for now. It can be worked around by using
INOUT parameters. Future improvements would be mainly syntax/parsing
adjustments; the guts that I'm implementing here would remain valid.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 127f3716a28cceca5077786e2cb3717e36dbb426 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 27 Feb 2018 09:55:32 -0500
Subject: [PATCH v1 1/2] fixup! Add prokind column, replacing proisagg and
proiswindow
---
src/backend/commands/dropcmds.c | 2 +-
src/backend/parser/parse_func.c | 6 +++---
src/backend/utils/cache/lsyscache.c | 12 ++++++------
src/include/utils/lsyscache.h | 2 +-
4 files changed, 11 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index fc4ce8d22a..45493abf57 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -92,7 +92,7 @@ RemoveObjects(DropStmt *stmt)
*/
if (stmt->removeType == OBJECT_FUNCTION)
{
- if (get_func_isagg(address.objectId))
+ if (get_func_kind(address.objectId) ==
PROKIND_AGGREGATE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("\"%s\" is an aggregate
function",
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9dbf2c2b63..0b5145f70d 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2078,7 +2078,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs
*func, bool noError)
if (objtype == OBJECT_FUNCTION)
{
/* Make sure it's a function, not a procedure */
- if (oid && get_func_rettype(oid) == InvalidOid)
+ if (oid && get_func_kind(oid) == PROKIND_PROCEDURE)
{
if (noError)
return InvalidOid;
@@ -2109,7 +2109,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs
*func, bool noError)
}
/* Make sure it's a procedure */
- if (get_func_rettype(oid) != InvalidOid)
+ if (get_func_kind(oid) != PROKIND_PROCEDURE)
{
if (noError)
return InvalidOid;
@@ -2145,7 +2145,7 @@ LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs
*func, bool noError)
}
/* Make sure it's an aggregate */
- if (!get_func_isagg(oid))
+ if (get_func_kind(oid) != PROKIND_AGGREGATE)
{
if (noError)
return InvalidOid;
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index 161470aa34..869a937d5a 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1600,20 +1600,20 @@ func_parallel(Oid funcid)
}
/*
- * get_func_isagg
- * Given procedure id, return whether the function is an aggregate.
+ * get_func_kind
+ * Given procedure id, return the function kind (prokind).
*/
-bool
-get_func_isagg(Oid funcid)
+char
+get_func_kind(Oid funcid)
{
HeapTuple tp;
- bool result;
+ char result;
tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
if (!HeapTupleIsValid(tp))
elog(ERROR, "cache lookup failed for function %u", funcid);
- result = ((Form_pg_proc) GETSTRUCT(tp))->prokind == PROKIND_AGGREGATE;
+ result = ((Form_pg_proc) GETSTRUCT(tp))->prokind;
ReleaseSysCache(tp);
return result;
}
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 1f6c04a8f3..3014cabb35 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -117,7 +117,7 @@ extern bool get_func_retset(Oid funcid);
extern bool func_strict(Oid funcid);
extern char func_volatile(Oid funcid);
extern char func_parallel(Oid funcid);
-extern bool get_func_isagg(Oid funcid);
+extern char get_func_kind(Oid funcid);
extern bool get_func_leakproof(Oid funcid);
extern float4 get_func_cost(Oid funcid);
extern float4 get_func_rows(Oid funcid);
base-commit: bc1adc651b8e60680aea144d51ae8bc78ea6b2fb
prerequisite-patch-id: f46ead32b2ef64d66cc1994da7db6e57857c0270
prerequisite-patch-id: 62e1cab8d489dfd26a2222f5b0f48f17a48cb63f
--
2.16.2
From 51725d637e343dde7438ea232f1cb1d16e8ad0a1 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 28 Feb 2018 16:41:24 -0500
Subject: [PATCH v1 2/2] Support INOUT parameters in procedures
In a top-level CALL, the values of INOUT parameters will be returned as
a result row. In PL/pgSQL, the values are assigned back to the input
parameters.
---
src/backend/commands/functioncmds.c | 44 ++++++-
src/backend/tcop/utility.c | 3 +-
src/include/commands/defrem.h | 3 +-
src/pl/plpgsql/src/expected/plpgsql_call.out | 60 +++++++++-
.../plpgsql/src/expected/plpgsql_transaction.out | 2 +-
src/pl/plpgsql/src/pl_comp.c | 1 +
src/pl/plpgsql/src/pl_exec.c | 131 ++++++++++++++++++++-
src/pl/plpgsql/src/pl_funcs.c | 25 ++++
src/pl/plpgsql/src/pl_gram.y | 35 +++++-
src/pl/plpgsql/src/pl_scanner.c | 2 +
src/pl/plpgsql/src/plpgsql.h | 13 ++
src/pl/plpgsql/src/sql/plpgsql_call.sql | 54 +++++++++
12 files changed, 358 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index 29380f61c3..ac12af3a67 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -68,6 +68,7 @@
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"
+#include "utils/typcache.h"
#include "utils/tqual.h"
/*
@@ -281,7 +282,7 @@ interpret_function_parameter_list(ParseState *pstate,
if (objtype == OBJECT_PROCEDURE)
{
- if (fp->mode == FUNC_PARAM_OUT || fp->mode ==
FUNC_PARAM_INOUT)
+ if (fp->mode == FUNC_PARAM_OUT)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
(errmsg("procedures cannot
have OUT parameters"))));
@@ -302,7 +303,9 @@ interpret_function_parameter_list(ParseState *pstate,
/* handle output parameters */
if (fp->mode != FUNC_PARAM_IN && fp->mode !=
FUNC_PARAM_VARIADIC)
{
- if (outCount == 0) /* save first output param's
type */
+ if (objtype == OBJECT_PROCEDURE)
+ *requiredResultType = RECORDOID;
+ else if (outCount == 0) /* save first output param's
type */
*requiredResultType = toid;
outCount++;
}
@@ -1005,7 +1008,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
{
Assert(!stmt->returnType);
- prorettype = InvalidOid;
+ prorettype = requiredResultType;
returnsSet = false;
}
else if (stmt->returnType)
@@ -2203,7 +2206,7 @@ ExecuteDoStmt(DoStmt *stmt, bool atomic)
* commits that might occur inside the procedure.
*/
void
-ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic)
+ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic,
DestReceiver *dest)
{
ListCell *lc;
FuncExpr *fexpr;
@@ -2216,6 +2219,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic)
EState *estate;
ExprContext *econtext;
HeapTuple tp;
+ Datum retval;
fexpr = stmt->funcexpr;
Assert(fexpr);
@@ -2282,7 +2286,37 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic)
i++;
}
- FunctionCallInvoke(&fcinfo);
+ retval = FunctionCallInvoke(&fcinfo);
+
+ if (fexpr->funcresulttype == RECORDOID && !fcinfo.isnull)
+ {
+ HeapTupleHeader td;
+ Oid tupType;
+ int32 tupTypmod;
+ TupleDesc retdesc;
+ HeapTupleData rettupdata;
+ TupOutputState *tstate;
+ TupleTableSlot *slot;
+
+ td = DatumGetHeapTupleHeader(retval);
+ tupType = HeapTupleHeaderGetTypeId(td);
+ tupTypmod = HeapTupleHeaderGetTypMod(td);
+ retdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+
+ rettupdata.t_len = HeapTupleHeaderGetDatumLength(td);
+ ItemPointerSetInvalid(&(rettupdata.t_self));
+ rettupdata.t_tableOid = InvalidOid;
+ rettupdata.t_data = td;
+
+ tstate = begin_tup_output_tupdesc(dest, retdesc);
+
+ slot = ExecStoreTuple(&rettupdata, tstate->slot, InvalidBuffer,
false);
+ tstate->dest->receiveSlot(slot, tstate->dest);
+
+ end_tup_output(tstate);
+
+ ReleaseTupleDesc(retdesc);
+ }
FreeExecutorState(estate);
}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index f78efdf359..6effe031f8 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -661,7 +661,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_CallStmt:
ExecuteCallStmt(castNode(CallStmt, parsetree), params,
- (context !=
PROCESS_UTILITY_TOPLEVEL || IsTransactionBlock()));
+ (context !=
PROCESS_UTILITY_TOPLEVEL || IsTransactionBlock()),
+ dest);
break;
case T_ClusterStmt:
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index c829abfea7..8fc9e424cf 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -17,6 +17,7 @@
#include "catalog/objectaddress.h"
#include "nodes/params.h"
#include "nodes/parsenodes.h"
+#include "tcop/dest.h"
#include "utils/array.h"
/* commands/dropcmds.c */
@@ -62,7 +63,7 @@ extern void DropTransformById(Oid transformOid);
extern void IsThereFunctionInNamespace(const char *proname, int pronargs,
oidvector *proargtypes, Oid
nspOid);
extern void ExecuteDoStmt(DoStmt *stmt, bool atomic);
-extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic);
+extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic,
DestReceiver *dest);
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool
missing_ok);
extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok);
extern void interpret_function_parameter_list(ParseState *pstate,
diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out
b/src/pl/plpgsql/src/expected/plpgsql_call.out
index e2442c603c..7d04136112 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -19,7 +19,7 @@ END;
$$;
CALL test_proc2();
ERROR: cannot return a value from a procedure
-CONTEXT: PL/pgSQL function test_proc2() while casting return value to
function's return type
+CONTEXT: PL/pgSQL function test_proc2() line 3 at RETURN
CREATE TABLE test1 (a int);
CREATE PROCEDURE test_proc3(x int)
LANGUAGE plpgsql
@@ -53,6 +53,64 @@ SELECT * FROM test1;
66
(2 rows)
+-- OUT parameters
+CREATE PROCEDURE test_proc5(a int, INOUT b int, INOUT c int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ b := b * a;
+ c := c * a;
+END;
+$$;
+CALL test_proc5(2, 3, 4);
+ b | c
+---+---
+ 6 | 8
+(1 row)
+
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ CALL test_proc5(2, x, y);
+ RAISE INFO 'x = %, y = %', x, y;
+END;
+$$;
+INFO: x = 6, y = 8
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ CALL test_proc5(2, x + 1, y); -- error
+ RAISE INFO 'x = %, y = %', x, y;
+END;
+$$;
+ERROR: argument 2 is an output parameter but is not writable
+CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ FOR i IN 1..5 LOOP
+ CALL test_proc5(i, x, y);
+ RAISE INFO 'x = %, y = %', x, y;
+ END LOOP;
+END;
+$$;
+INFO: x = 3, y = 4
+INFO: x = 6, y = 8
+INFO: x = 18, y = 24
+INFO: x = 72, y = 96
+INFO: x = 360, y = 480
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
diff --git a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
index 8ec22c646c..ce66487137 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -98,7 +98,7 @@ SELECT transaction_test3();
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
SQL statement "CALL transaction_test1()"
-PL/pgSQL function transaction_test3() line 3 at SQL statement
+PL/pgSQL function transaction_test3() line 3 at CALL
SELECT * FROM test1;
a | b
---+---
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index d07a16a7ea..7137e5dd37 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -351,6 +351,7 @@ do_compile(FunctionCallInfo fcinfo,
function->fn_tid = procTup->t_self;
function->fn_input_collation = fcinfo->fncollation;
function->fn_cxt = func_cxt;
+ function->fn_is_procedure = (get_func_kind(function->fn_oid) ==
PROKIND_PROCEDURE);
function->out_param_varno = -1; /* set up for no OUT param */
function->resolve_option = plpgsql_variable_conflict;
function->print_strict_params = plpgsql_print_strict_params;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index eae51e316a..f7630a96bb 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -24,6 +24,7 @@
#include "catalog/pg_type.h"
#include "executor/execExpr.h"
#include "executor/spi.h"
+#include "executor/spi_priv.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -40,6 +41,7 @@
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
+#include "utils/syscache.h"
#include "utils/typcache.h"
#include "plpgsql.h"
@@ -253,6 +255,8 @@ static int exec_stmt_assign(PLpgSQL_execstate *estate,
PLpgSQL_stmt_assign *stmt);
static int exec_stmt_perform(PLpgSQL_execstate *estate,
PLpgSQL_stmt_perform *stmt);
+static int exec_stmt_call(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_call *stmt);
static int exec_stmt_getdiag(PLpgSQL_execstate *estate,
PLpgSQL_stmt_getdiag *stmt);
static int exec_stmt_if(PLpgSQL_execstate *estate,
@@ -573,7 +577,7 @@ plpgsql_exec_function(PLpgSQL_function *func,
FunctionCallInfo fcinfo,
estate.err_text = NULL;
estate.err_stmt = (PLpgSQL_stmt *) (func->action);
rc = exec_stmt_block(&estate, func->action);
- if (rc != PLPGSQL_RC_RETURN && func->fn_rettype)
+ if (rc != PLPGSQL_RC_RETURN && !func->fn_is_procedure)
{
estate.err_stmt = NULL;
estate.err_text = NULL;
@@ -617,10 +621,6 @@ plpgsql_exec_function(PLpgSQL_function *func,
FunctionCallInfo fcinfo,
}
else if (!estate.retisnull)
{
- if (!func->fn_rettype)
- ereport(ERROR,
- (errmsg("cannot return a value from a
procedure")));
-
/*
* Cast result value to function's declared result type, and
copy it
* out to the upper executor memory context. We must treat
tuple
@@ -1905,6 +1905,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
rc = exec_stmt_perform(estate, (PLpgSQL_stmt_perform *)
stmt);
break;
+ case PLPGSQL_STMT_CALL:
+ rc = exec_stmt_call(estate, (PLpgSQL_stmt_call *) stmt);
+ break;
+
case PLPGSQL_STMT_GETDIAG:
rc = exec_stmt_getdiag(estate, (PLpgSQL_stmt_getdiag *)
stmt);
break;
@@ -2045,6 +2049,116 @@ exec_stmt_perform(PLpgSQL_execstate *estate,
PLpgSQL_stmt_perform *stmt)
return PLPGSQL_RC_OK;
}
+/*
+ * exec_stmt_call
+ */
+static int
+exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
+{
+ PLpgSQL_expr *expr = stmt->expr;
+ ParamListInfo paramLI;
+ int rc;
+
+ if (expr->plan == NULL)
+ exec_prepare_plan(estate, expr, 0);
+
+ paramLI = setup_param_list(estate, expr);
+
+ rc = SPI_execute_plan_with_paramlist(expr->plan, paramLI,
+
estate->readonly_func, 0);
+
+ if (rc < 0)
+ elog(ERROR, "SPI_execute_plan_with_paramlist failed executing
query \"%s\": %s",
+ expr->query, SPI_result_code_string(rc));
+
+ if (SPI_processed == 1)
+ {
+ SPITupleTable *tuptab = SPI_tuptable;
+
+ /*
+ * Construct a dummy target row based on the OUT/INOUT
parameters of
+ * the procedure call.
+ */
+ if (!stmt->target)
+ {
+ Node *node;
+ ListCell *lc;
+ FuncExpr *funcexpr;
+ int i;
+ HeapTuple tuple;
+ int numargs;
+ Oid *argtypes;
+ char **argnames;
+ char *argmodes;
+ PLpgSQL_row *row;
+ int nfields;
+
+ /*
+ * Get the original CallStmt
+ */
+ node = linitial_node(Query, ((CachedPlanSource *)
linitial(expr->plan->plancache_list))->query_list)->utilityStmt;
+ if (!IsA(node, CallStmt))
+ elog(ERROR, "returned row from not a CallStmt");
+
+ funcexpr = castNode(CallStmt, node)->funcexpr;
+
+ /*
+ * Get the parameter modes
+ */
+ tuple = SearchSysCache1(PROCOID,
ObjectIdGetDatum(funcexpr->funcid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for function
%u", funcexpr->funcid);
+ numargs = get_func_arg_info(tuple, &argtypes,
&argnames, &argmodes);
+ ReleaseSysCache(tuple);
+
+ Assert(numargs == list_length(funcexpr->args));
+
+ /*
+ * Construct row
+ */
+ row = palloc0(sizeof(*row));
+ row->dtype = PLPGSQL_DTYPE_ROW;
+ row->lineno = -1;
+ row->varnos = palloc(sizeof(int) * FUNC_MAX_ARGS);
+
+ nfields = 0;
+ i = 0;
+ foreach (lc, funcexpr->args)
+ {
+ Node *n = lfirst(lc);
+
+ if (argmodes && (argmodes[i] ==
PROARGMODE_INOUT || argmodes[i] == PROARGMODE_OUT))
+ {
+ Param *param;
+
+ if (!IsA(n, Param))
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+
errmsg("argument %d is an output parameter but is not writable", i + 1)));
+
+ param = castNode(Param, n);
+ /* paramid is offset by 1 (see
make_datum_param()) */
+ row->varnos[nfields++] = param->paramid
- 1;
+ }
+ i++;
+ }
+
+ row->nfields = nfields;
+
+ stmt->target = (PLpgSQL_variable *) row;
+ }
+
+ exec_move_row(estate, stmt->target, tuptab->vals[0],
tuptab->tupdesc);
+ }
+ else if (SPI_processed > 1)
+ elog(ERROR, "procedure call returned more than one row");
+
+ exec_eval_cleanup(estate);
+ SPI_freetuptable(SPI_tuptable);
+
+ return PLPGSQL_RC_OK;
+}
+
/* ----------
* exec_stmt_getdiag Put internal PG
information into
*
specified variables.
@@ -2835,6 +2949,13 @@ exec_stmt_exit(PLpgSQL_execstate *estate,
PLpgSQL_stmt_exit *stmt)
static int
exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
{
+ if (estate->func->fn_is_procedure)
+ {
+ if ((stmt->retvarno >= 0 && stmt->retvarno !=
estate->func->out_param_varno) || stmt->expr)
+ ereport(ERROR,
+ (errmsg("cannot return a value from a
procedure")));
+ }
+
/*
* If processing a set-returning PL/pgSQL function, the final RETURN
* indicates that the function is finished producing tuples. The rest
of
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index b986fc39b3..39d6a54663 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -284,6 +284,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
return "CLOSE";
case PLPGSQL_STMT_PERFORM:
return "PERFORM";
+ case PLPGSQL_STMT_CALL:
+ return "CALL";
case PLPGSQL_STMT_COMMIT:
return "COMMIT";
case PLPGSQL_STMT_ROLLBACK:
@@ -367,6 +369,7 @@ static void free_open(PLpgSQL_stmt_open *stmt);
static void free_fetch(PLpgSQL_stmt_fetch *stmt);
static void free_close(PLpgSQL_stmt_close *stmt);
static void free_perform(PLpgSQL_stmt_perform *stmt);
+static void free_call(PLpgSQL_stmt_call *stmt);
static void free_commit(PLpgSQL_stmt_commit *stmt);
static void free_rollback(PLpgSQL_stmt_rollback *stmt);
static void free_expr(PLpgSQL_expr *expr);
@@ -449,6 +452,9 @@ free_stmt(PLpgSQL_stmt *stmt)
case PLPGSQL_STMT_PERFORM:
free_perform((PLpgSQL_stmt_perform *) stmt);
break;
+ case PLPGSQL_STMT_CALL:
+ free_call((PLpgSQL_stmt_call *) stmt);
+ break;
case PLPGSQL_STMT_COMMIT:
free_commit((PLpgSQL_stmt_commit *) stmt);
break;
@@ -602,6 +608,12 @@ free_perform(PLpgSQL_stmt_perform *stmt)
free_expr(stmt->expr);
}
+static void
+free_call(PLpgSQL_stmt_call *stmt)
+{
+ free_expr(stmt->expr);
+}
+
static void
free_commit(PLpgSQL_stmt_commit *stmt)
{
@@ -805,6 +817,7 @@ static void dump_fetch(PLpgSQL_stmt_fetch *stmt);
static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt);
static void dump_close(PLpgSQL_stmt_close *stmt);
static void dump_perform(PLpgSQL_stmt_perform *stmt);
+static void dump_call(PLpgSQL_stmt_call *stmt);
static void dump_commit(PLpgSQL_stmt_commit *stmt);
static void dump_rollback(PLpgSQL_stmt_rollback *stmt);
static void dump_expr(PLpgSQL_expr *expr);
@@ -897,6 +910,9 @@ dump_stmt(PLpgSQL_stmt *stmt)
case PLPGSQL_STMT_PERFORM:
dump_perform((PLpgSQL_stmt_perform *) stmt);
break;
+ case PLPGSQL_STMT_CALL:
+ dump_call((PLpgSQL_stmt_call *) stmt);
+ break;
case PLPGSQL_STMT_COMMIT:
dump_commit((PLpgSQL_stmt_commit *) stmt);
break;
@@ -1275,6 +1291,15 @@ dump_perform(PLpgSQL_stmt_perform *stmt)
printf("\n");
}
+static void
+dump_call(PLpgSQL_stmt_call *stmt)
+{
+ dump_ind();
+ printf("CALL expr = ");
+ dump_expr(stmt->expr);
+ printf("\n");
+}
+
static void
dump_commit(PLpgSQL_stmt_commit *stmt)
{
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 688fbd6531..9429cb6334 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -196,7 +196,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%type <stmt> proc_stmt pl_block
%type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type <stmt> stmt_return stmt_raise stmt_assert stmt_execsql
-%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
+%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_call stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
%type <stmt> stmt_commit stmt_rollback
%type <stmt> stmt_case stmt_foreach_a
@@ -256,6 +256,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_BACKWARD
%token <keyword> K_BEGIN
%token <keyword> K_BY
+%token <keyword> K_CALL
%token <keyword> K_CASE
%token <keyword> K_CLOSE
%token <keyword> K_COLLATE
@@ -274,6 +275,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_DEFAULT
%token <keyword> K_DETAIL
%token <keyword> K_DIAGNOSTICS
+%token <keyword> K_DO
%token <keyword> K_DUMP
%token <keyword> K_ELSE
%token <keyword> K_ELSIF
@@ -871,6 +873,8 @@ proc_stmt : pl_block ';'
{ $$ = $1; }
| stmt_perform
{ $$ = $1; }
+ | stmt_call
+ { $$ = $1; }
| stmt_getdiag
{ $$ = $1; }
| stmt_open
@@ -902,6 +906,33 @@ stmt_perform : K_PERFORM expr_until_semi
}
;
+stmt_call : K_CALL
+ {
+ PLpgSQL_stmt_call *new;
+
+ new =
palloc0(sizeof(PLpgSQL_stmt_call));
+ new->cmd_type =
PLPGSQL_STMT_CALL;
+ new->lineno =
plpgsql_location_to_lineno(@1);
+ new->expr = read_sql_stmt("CALL
");
+
+ $$ = (PLpgSQL_stmt *)new;
+
+ }
+ | K_DO
+ {
+ /* use the same structures as
for CALL, for simplicity */
+ PLpgSQL_stmt_call *new;
+
+ new =
palloc0(sizeof(PLpgSQL_stmt_call));
+ new->cmd_type =
PLPGSQL_STMT_CALL;
+ new->lineno =
plpgsql_location_to_lineno(@1);
+ new->expr = read_sql_stmt("DO
");
+
+ $$ = (PLpgSQL_stmt *)new;
+
+ }
+ ;
+
stmt_assign : assign_var assign_operator expr_until_semi
{
PLpgSQL_stmt_assign *new;
@@ -2400,6 +2431,7 @@ unreserved_keyword :
| K_ARRAY
| K_ASSERT
| K_BACKWARD
+ | K_CALL
| K_CLOSE
| K_COLLATE
| K_COLUMN
@@ -2416,6 +2448,7 @@ unreserved_keyword :
| K_DEFAULT
| K_DETAIL
| K_DIAGNOSTICS
+ | K_DO
| K_DUMP
| K_ELSIF
| K_ERRCODE
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index 12a3e6b818..08614a89a8 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -102,6 +102,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("assert", K_ASSERT, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
+ PG_KEYWORD("call", K_CALL, UNRESERVED_KEYWORD)
PG_KEYWORD("close", K_CLOSE, UNRESERVED_KEYWORD)
PG_KEYWORD("collate", K_COLLATE, UNRESERVED_KEYWORD)
PG_KEYWORD("column", K_COLUMN, UNRESERVED_KEYWORD)
@@ -118,6 +119,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("default", K_DEFAULT, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
PG_KEYWORD("diagnostics", K_DIAGNOSTICS, UNRESERVED_KEYWORD)
+ PG_KEYWORD("do", K_DO, UNRESERVED_KEYWORD)
PG_KEYWORD("dump", K_DUMP, UNRESERVED_KEYWORD)
PG_KEYWORD("elseif", K_ELSIF, UNRESERVED_KEYWORD)
PG_KEYWORD("elsif", K_ELSIF, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 26a7344e9a..c920b8601e 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -125,6 +125,7 @@ typedef enum PLpgSQL_stmt_type
PLPGSQL_STMT_FETCH,
PLPGSQL_STMT_CLOSE,
PLPGSQL_STMT_PERFORM,
+ PLPGSQL_STMT_CALL,
PLPGSQL_STMT_COMMIT,
PLPGSQL_STMT_ROLLBACK
} PLpgSQL_stmt_type;
@@ -508,6 +509,17 @@ typedef struct PLpgSQL_stmt_perform
PLpgSQL_expr *expr;
} PLpgSQL_stmt_perform;
+/*
+ * CALL statement
+ */
+typedef struct PLpgSQL_stmt_call
+{
+ PLpgSQL_stmt_type cmd_type;
+ int lineno;
+ PLpgSQL_expr *expr;
+ PLpgSQL_variable *target;
+} PLpgSQL_stmt_call;
+
/*
* COMMIT statement
*/
@@ -910,6 +922,7 @@ typedef struct PLpgSQL_function
Oid fn_input_collation;
PLpgSQL_func_hashkey *fn_hashkey; /* back-link to hashtable key */
MemoryContext fn_cxt;
+ bool fn_is_procedure;
Oid fn_rettype;
int fn_rettyplen;
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql
b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index 321ed43af8..38763014ea 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -57,6 +57,60 @@ CREATE PROCEDURE test_proc4(y int)
SELECT * FROM test1;
+-- OUT parameters
+CREATE PROCEDURE test_proc5(a int, INOUT b int, INOUT c int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ b := b * a;
+ c := c * a;
+END;
+$$;
+
+CALL test_proc5(2, 3, 4);
+
+
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ CALL test_proc5(2, x, y);
+ RAISE INFO 'x = %, y = %', x, y;
+END;
+$$;
+
+
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ CALL test_proc5(2, x + 1, y); -- error
+ RAISE INFO 'x = %, y = %', x, y;
+END;
+$$;
+
+
+DO
+LANGUAGE plpgsql
+$$
+DECLARE
+ x int := 3;
+ y int := 4;
+BEGIN
+ FOR i IN 1..5 LOOP
+ CALL test_proc5(i, x, y);
+ RAISE INFO 'x = %, y = %', x, y;
+ END LOOP;
+END;
+$$;
+
+
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
DROP PROCEDURE test_proc3;
--
2.16.2
