diff options
28 files changed, 416 insertions, 93 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0e580b157f5..3927b1030df 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.sgml index c2bb3e32685..74b6b258780 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -479,6 +479,14 @@ $$ LANGUAGE plpgsql; </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 @@ -498,6 +511,31 @@ $$ LANGUAGE plpgsql; </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.sgml index c819c7bb4e3..38fd60128b7 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.sgml index bcf45c7a85f..5c176fb5d87 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.sgml index 6e8ced3eaf1..eda91b4e240 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.sgml index 36c307cadc7..e258eca5cee 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.sgml index 6da266ae2da..bf2c6ce1aaa 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.sgml index e9688cce214..9b87bcd5196 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.sgml index 8c74c11d3b5..2863f7c2065 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/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 40d65dc6bab..f7dab9925b9 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -249,6 +249,9 @@ ProcedureCreate(const char *procedureName, elog(ERROR, "variadic parameter must be last"); break; case PROARGMODE_OUT: + if (OidIsValid(variadicType) && prokind == PROKIND_PROCEDURE) + elog(ERROR, "variadic parameter must be last"); + break; case PROARGMODE_TABLE: /* okay */ break; @@ -462,10 +465,12 @@ ProcedureCreate(const char *procedureName, if (isnull) proargmodes = PointerGetDatum(NULL); /* just to be sure */ - n_old_arg_names = get_func_input_arg_names(proargnames, + n_old_arg_names = get_func_input_arg_names(prokind, + proargnames, proargmodes, &old_arg_names); - n_new_arg_names = get_func_input_arg_names(parameterNames, + n_new_arg_names = get_func_input_arg_names(prokind, + parameterNames, parameterModes, &new_arg_names); for (j = 0; j < n_old_arg_names; j++) diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index e236581a8e0..c3ce480c8f5 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,25 +281,21 @@ 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) + isinput = true; + + /* handle signature parameters */ + if (fp->mode == FUNC_PARAM_IN || fp->mode == FUNC_PARAM_INOUT || + (objtype == OBJECT_PROCEDURE && fp->mode == FUNC_PARAM_OUT) || + fp->mode == FUNC_PARAM_VARIADIC) { - /* other input parameters can't follow a VARIADIC parameter */ + /* other signature parameters can't follow a VARIADIC parameter */ if (varCount > 0) ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), - errmsg("VARIADIC parameter must be the last input parameter"))); - inTypes[inCount++] = toid; - isinput = true; + errmsg("VARIADIC parameter must be the last signature parameter"))); + sigArgTypes[sigArgCount++] = toid; } /* handle output parameters */ @@ -429,7 +425,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 +2063,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 +2126,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 +2157,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/executor/functions.c b/src/backend/executor/functions.c index f940f48c6da..bf00a9c1e8d 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -259,7 +259,8 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple, if (isNull) proargmodes = PointerGetDatum(NULL); /* just to be sure */ - n_arg_names = get_func_input_arg_names(proargnames, proargmodes, + n_arg_names = get_func_input_arg_names(procedureStruct->prokind, + proargnames, proargmodes, &pinfo->argnames); /* Paranoia: ignore the result if too few array entries */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 17653ef3a79..0d101d81715 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))); @@ -7447,6 +7460,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/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c index 78ed8572038..b9efa772915 100644 --- a/src/backend/utils/fmgr/funcapi.c +++ b/src/backend/utils/fmgr/funcapi.c @@ -1233,7 +1233,8 @@ get_func_trftypes(HeapTuple procTup, * are set to NULL. You don't get anything if proargnames is NULL. */ int -get_func_input_arg_names(Datum proargnames, Datum proargmodes, +get_func_input_arg_names(char prokind, + Datum proargnames, Datum proargmodes, char ***arg_names) { ArrayType *arr; @@ -1291,6 +1292,7 @@ get_func_input_arg_names(Datum proargnames, Datum proargmodes, if (argmodes == NULL || argmodes[i] == PROARGMODE_IN || argmodes[i] == PROARGMODE_INOUT || + (argmodes[i] == PROARGMODE_OUT && prokind == PROKIND_PROCEDURE) || argmodes[i] == PROARGMODE_VARIADIC) { char *pname = TextDatumGetCString(argnames[i]); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b50fa25dbd8..268c8108965 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/include/funcapi.h b/src/include/funcapi.h index b047acdc1a8..2f46442087e 100644 --- a/src/include/funcapi.h +++ b/src/include/funcapi.h @@ -172,7 +172,8 @@ extern int get_func_arg_info(HeapTuple procTup, Oid **p_argtypes, char ***p_argnames, char **p_argmodes); -extern int get_func_input_arg_names(Datum proargnames, Datum proargmodes, +extern int get_func_input_arg_names(char prokind, + Datum proargnames, Datum proargmodes, char ***arg_names); extern int get_func_trftypes(HeapTuple procTup, Oid **p_trftypes); diff --git a/src/pl/plperl/expected/plperl_call.out b/src/pl/plperl/expected/plperl_call.out index c55c59cbceb..a08b9ff795c 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.sql index 2cf5461fefd..bbea85fc9f5 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.out index d9c88e85c8d..97385716110 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.c index e7f4a5f291d..344627da956 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.sql index 4702bd14d12..d506809ddbf 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.out index 07ae04e98ba..c3f3c8e95e5 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.c index 9e158396111..ec47f52e61d 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.sql index 2f792f92bd7..46e89b1a9e1 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.out index d290c8fbd05..f0eb356cf23 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.sql index 95791d08bee..963277e1fb8 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/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 211a42cefa0..3838fa2324d 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.sql index 89b96d580ff..2ef1c82ceab 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 |