Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kMwFp-0005D4-St for pgsql-hackers@arkaria.postgresql.org; Mon, 28 Sep 2020 16:43:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kMwFl-0000gL-BL for pgsql-hackers@arkaria.postgresql.org; Mon, 28 Sep 2020 16:43:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kMwFk-0000gE-TR for pgsql-hackers@lists.postgresql.org; Mon, 28 Sep 2020 16:43:53 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kMwFc-0008GG-PP for pgsql-hackers@postgresql.org; Mon, 28 Sep 2020 16:43:52 +0000 Received: by mail-ot1-x336.google.com with SMTP id q21so1560988ota.8 for ; Mon, 28 Sep 2020 09:43:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=from:subject:to:references:autocrypt:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=qmV5080vJWm5dxOJWMzH0nb3ORCb+JCk9/b66kXfcQc=; b=bX9BCLkJ/QC38aC7Gp/n8D8GoBtz6wq88jUBRXxjCeIC6qpL1M2NozR+rQUBp7v/BY QBCQE97E6MsO2r2yWVoY8pxDNZ33pKLAwe1dmEpoIq2tanyDXXcXEK2OO7ReI7R9XG8z 2OI+0HqA/kun56hb4XgqI54dW5Eo/tE1+crmsdZ7fn+s0PoOXzbCS5vbzMNZuSmnEika PAJIfZ6++gjv0vV7Qu0Yua2vO7bPBKR2hfAKhXM413hb6rDmcjSGhV0Zj/fEmDqkn5Mc pE0KQZhMxoqMcKqqk5nX3mIqW2QLm1FUrpP6XP1mqGOE7G63mK3PDgWQ68EMTCBLM+mv bpgA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:subject:to:references:autocrypt:message-id :date:user-agent:mime-version:in-reply-to:content-language; bh=qmV5080vJWm5dxOJWMzH0nb3ORCb+JCk9/b66kXfcQc=; b=fZJLHbpeMLu8sCWs7kqs7aiS94EJcGr6aH3GFwr49p0nZc6j/Ax/HL5KQsIDeQuEIK VOz/XMaLi/DRXv4vL7rhVDtwL2ms+tV+/p2NdZVegwmhZU9OmIF1P9CLbXLSKd1PLRzy XeRWyPfNUxU71waT2rDUeaL7i7lyY+OPc2Vl4Ugu5MwXOzLc/vI60zeIx3Ayz+EyPFLs +8lSjO7uq1SjA+5kJA8hY0yMoRj3JOh0coMpB8eg8QTwe18GeEly0gdyk4hfpFac8Drn kApYZZikVdbuRn4SDz5OWAUIjf58AUohbgbQ0kVes18qIc++XUjiW0iaMBfHjVR1J5or a/5Q== X-Gm-Message-State: AOAM531hZILjxt5+AI+5Tf3G4thDkU/e54jkMwK2UaONnA7OjNJPcmsU 00YEBCCXfGDvLVVQuqeMCTfYCtTb4BbeS1opQpKn10GqgaRz1aK+W9IXu2acfTI76DBE1um6BHN +amwZdgiXQ7zZKLYK43QI9ZtSvefwgszftsDDTLiZcd7D7VkEyyUvvZ4iz8ZUl+QJ/Jo3xRMa6H xJLhHE628B4La2O39tDA== X-Google-Smtp-Source: ABdhPJwSn5J2DEJTeMbJW823zc1gmqsZA8QRGGh9/U4yEXReP89HDbc12+gjWgicoIwUdYsqr9p+EA== X-Received: by 2002:a05:6830:1d5:: with SMTP id r21mr1660298ota.244.1601311422114; Mon, 28 Sep 2020 09:43:42 -0700 (PDT) Received: from ?IPv6:2600:1700:722:4630::43? ([2600:1700:722:4630::43]) by smtp.gmail.com with ESMTPSA id c7sm337189ots.20.2020.09.28.09.43.40 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 28 Sep 2020 09:43:40 -0700 (PDT) From: Andrew Dunstan X-Google-Original-From: Andrew Dunstan Subject: Re: Support for OUT parameters in procedures To: Peter Eisentraut , pgsql-hackers References: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com> Autocrypt: addr=andrew.dunstan@2ndQuadrant.com; prefer-encrypt=mutual; keydata= mQENBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB AAG0L0FuZHJldyBEdW5zdGFuIDxhbmRyZXcuZHVuc3RhbkAybmRRdWFkcmFudC5jb20+iQFY BBMBAgBCAhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ /DuBAhkBBQJd1pSYBQkSzqMsAAoJEJn6f8tZ/DuBJmEIAIVFBIC3qGqh+YTPVTkfowwxpHz0 tZSPjZ6GS/5y62kgoZrZOVXw+Rg2ceuMM3a+In/RRjTx4bembgYqRKgzXlDfiNBJeq5ioVuN H1faDKE2lm5a6QkRQ2JU6DKf60fqSJGP5LisCUr1V0wz6EnlO31ArrKtXCNB+yk0KDAS6o6I Ql4pkXpr+vq4iilwJmiJOqTBam0LmQE7ulYCCYU8Z6MkpiSl+dO7QSramcGyxauQRI3a7gcq dpo8Vrv3gFTQDeMYxk7cqZp08pIVH5XR0+LgBz4X84f59rA/VAKlC/r6CyEQk59rvDLxCMyP 2buoj51Z25N3MVghRDGcsU6mT7e5AQ0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oK Waqf+wmW4tpDe/VVcxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7 llV0xSSoFiieQBsrGvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8 BoVrRFcdzlIY/NFNhFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C 4R+M6w7o2PLGOjdZ+kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaY L++LAEOqrlvm2TvqQqZRtnEAEQEAAYkBJQQYAQIADwIbDAUCXdaUtAUJEs6jVQAKCRCZ+n/L Wfw7gQi5B/9sHbmRloZNCWWwZGm+O9iG/JGQxS0WWa4GaZqzqSTWOft2/gO0Bgag89r1djq4 PFgSZtzHvKQpqYz/7HkPrWh1jpcS83i/uYY1uyHXUUtpdEmn4buNObEtD0YNaJkGdhASsECq 6akw+u7WJYmAHs/SpR6ok6HqLw7JHDVc2h5dViLfTk0qrgDa7sq/LVMoJje5Ct/Ku3YaWAV6 YSmzoPP5BmPVFqP11OMQkCqurIu9vTLXKFWX623vCmqRDHJ6e5GCDHbH8USU+4wJBfPymZkd cA7NGm/mmWeJiWBFIDyjzYxrjku8ty9TZJVsaRXnS+OFQzXz9Eu2wTpKdHij46uY Message-ID: <4f733cca-5e07-e167-8b38-05b5c9066d04@2ndQuadrant.com> Date: Mon, 28 Sep 2020 12:43:39 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.9.0 MIME-Version: 1.0 In-Reply-To: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com> Content-Type: multipart/mixed; boundary="------------458FB4393C51720E98781C51" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------458FB4393C51720E98781C51 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit On 8/27/20 4:34 AM, Peter Eisentraut wrote: > Procedures currently don't allow OUT parameters.  The reason for this > is that at the time procedures were added (PG11), some of the details > of how this should work were unclear and the issue was postponed.  I > am now intending to resolve this. > > AFAICT, OUT parameters in _functions_ are not allowed per the SQL > standard, so whatever PostgreSQL is doing there at the moment is > mostly our own invention.  By contrast, I am here intending to make > OUT parameters in procedures work per SQL standard and be compatible > with the likes of PL/SQL. > > The main difference is that for procedures, OUT parameters are part of > the signature and need to be specified as part of the call.  This > makes sense for nested calls in PL/pgSQL like this: > > CREATE PROCEDURE test_proc(IN a int, OUT b int) > LANGUAGE plpgsql > AS $$ > BEGIN >   b := a * 2; > END; > $$; > > DO $$ > DECLARE _a int; _b int; > BEGIN >   _a := 10; >   CALL test_proc(_a, _b); >   RAISE NOTICE '_a: %, _b: %', _a, _b; > END > $$; > > For a top-level direct call, you can pass whatever you want, since all > OUT parameters are presented as initially NULL to the procedure code. > So you could just pass NULL, as in CALL test_proc(5, NULL). > > The code changes to make this happen are not as significant as I had > initially feared.  Most of the patch is expanded documentation and > additional tests.  In some cases, I changed the terminology from > "input parameters" to "signature parameters" to make the difference > clearer. Overall, while this introduces some additional conceptual > complexity, the way it works is pretty obvious in the end, and people > porting from other systems will find it working as expected. > I've reviewed this, and I think it's basically fine. I've made an addition that adds a test module that shows how this can be called from libpq - that should be helpful (I hope) for driver writers. A combined patch with the original plus my test suite is attached. I think this can be marked RFC. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------458FB4393C51720E98781C51 Content-Type: text/x-patch; charset=UTF-8; name="Support-for-OUT-parameters-in-procedures-v2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="Support-for-OUT-parameters-in-procedures-v2.patch" diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index de9bacd34f..da77bacf65 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -5875,8 +5875,9 @@ SCRAM-SHA-256$<iteration count>:&l An array with the data types of the function arguments. This includes only input arguments (including INOUT and - VARIADIC arguments), and thus represents - the call signature of the function. + VARIADIC arguments), as well as + OUT parameters of procedures, and thus represents + the call signature of the function or procedure. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 815912666d..309a714fc4 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -478,6 +478,14 @@ $$ LANGUAGE plpgsql; included it, but it would be redundant. + + To call a function with OUT parameters, omit the + output parameter in the function call: + +SELECT sales_tax(100.00); + + + 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 As discussed in , this @@ -497,6 +510,31 @@ $$ LANGUAGE plpgsql; RETURNS record. + + This also works with procedures, for example: + + +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; + + + In a call to a procedure, all the parameters must be specified. For + output parameters, NULL may be specified. + +CALL sum_n_product(2, 4, NULL, NULL); + sum | prod +-----+------ + 6 | 8 + + Output parameters in procedures become more interesting in nested calls, + where they can be assigned to variables. See for details. + + Another way to declare a PL/pgSQL function is with RETURNS TABLE, for example: diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index c819c7bb4e..38fd60128b 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -212,11 +212,12 @@ ALTER EXTENSION name DROP IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. - Note that ALTER EXTENSION does not actually pay - any attention to OUT arguments, since only the input - arguments are needed to determine the function's identity. - So it is sufficient to list the IN, INOUT, - and VARIADIC arguments. + Note that ALTER EXTENSION does not actually pay any + attention to OUT 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 IN, INOUT, and + VARIADIC arguments for functions and aggregates. diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index bcf45c7a85..5c176fb5d8 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -81,8 +81,9 @@ ALTER PROCEDURE name [ ( [ [ name [ - The mode of an argument: IN or VARIADIC. - If omitted, the default is IN. + The mode of an argument: IN, OUT, + INOUT, or VARIADIC. If omitted, + the default is IN. diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index e9688cce21..9b87bcd519 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -127,11 +127,12 @@ SECURITY LABEL [ FOR provider ] ON argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. - Note that SECURITY LABEL does not actually - pay any attention to OUT arguments, since only the input - arguments are needed to determine the function's identity. - So it is sufficient to list the IN, INOUT, - and VARIADIC arguments. + Note that SECURITY LABEL does not actually pay any + attention to OUT 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 IN, INOUT, and + VARIADIC arguments for functions and aggregates. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 0f60a4a0ab..90e4b0f2ff 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -179,6 +179,24 @@ SELECT clean_emp(); + + You can also write this as a procedure, thus avoiding the issue of the + return type. For example: + +CREATE PROCEDURE clean_emp() AS ' + DELETE FROM emp + WHERE salary < 0; +' LANGUAGE SQL; + +CALL clean_emp(); + + In simple cases like this, the difference between a function returning + void 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 void is a PostgreSQL extension. + + 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); + + <acronym>SQL</acronym> Procedures with Output Parameters + + + procedures + output parameter + + + + Output parameters are also supported in procedures, but they work a bit + differently from functions. Notably, output parameters + are included in the signature of a procedure and + must be specified in the procedure call. + + + + For example, the bank account debiting routine from earlier could be + written like this: + +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; + + To call this procedure, it is irrelevant what is passed as the argument + of the OUT parameter, so you could pass + NULL: + +CALL tp1(17, 100.0, NULL); + + + + + Procedures with output parameters are more useful in PL/pgSQL, where the + output parameters can be assigned to variables. See for details. + + + <acronym>SQL</acronym> Functions with Variable Numbers of Arguments diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index e236581a8e..3db1dbb08e 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,15 +281,6 @@ 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) { @@ -298,10 +289,16 @@ interpret_function_parameter_list(ParseState *pstate, ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("VARIADIC parameter must be the last input parameter"))); - inTypes[inCount++] = toid; isinput = true; } + if (fp->mode == FUNC_PARAM_IN || fp->mode == FUNC_PARAM_INOUT || + (objtype == OBJECT_PROCEDURE && fp->mode == FUNC_PARAM_OUT) || + fp->mode == FUNC_PARAM_VARIADIC) + { + sigArgTypes[sigArgCount++] = toid; + } + /* handle output parameters */ if (fp->mode != FUNC_PARAM_IN && fp->mode != FUNC_PARAM_VARIADIC) { @@ -429,7 +426,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 +2064,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 +2127,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 +2158,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/parser/gram.y b/src/backend/parser/gram.y index 17653ef3a7..0d101d8171 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 privilege %type privileges privilege_list %type privilege_target -%type function_with_argtypes aggregate_with_argtypes operator_with_argtypes -%type function_with_argtypes_list aggregate_with_argtypes_list operator_with_argtypes_list +%type function_with_argtypes aggregate_with_argtypes operator_with_argtypes procedure_with_argtypes function_with_argtypes_common +%type function_with_argtypes_list aggregate_with_argtypes_list operator_with_argtypes_list procedure_with_argtypes_list %type defacl_privilege_target %type DefACLOption %type 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))); @@ -7446,6 +7459,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/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b50fa25dbd..268c810896 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/pl/plperl/expected/plperl_call.out b/src/pl/plperl/expected/plperl_call.out index c55c59cbce..a08b9ff795 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 2cf5461fef..bbea85fc9f 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 d9c88e85c8..9738571611 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: +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 e7f4a5f291..344627da95 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 4702bd14d1..d506809ddb 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 07ae04e98b..c3f3c8e95e 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 9e15839611..ec47f52e61 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 2f792f92bd..46e89b1a9e 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 d290c8fbd0..f0eb356cf2 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 95791d08be..963277e1fb 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/modules/Makefile b/src/test/modules/Makefile index a6d2ffbf9e..287214c544 100644 --- a/src/test/modules/Makefile +++ b/src/test/modules/Makefile @@ -17,6 +17,7 @@ SUBDIRS = \ test_extensions \ test_ginpostinglist \ test_integerset \ + test_libpq \ test_misc \ test_parser \ test_pg_dump \ diff --git a/src/test/modules/test_libpq/Makefile b/src/test/modules/test_libpq/Makefile new file mode 100644 index 0000000000..ade53e2ad3 --- /dev/null +++ b/src/test/modules/test_libpq/Makefile @@ -0,0 +1,26 @@ +# +# Makefile for libpq tests +# + +subdir = src/test/modules/test_libpq +top_builddir = ../../../.. +include $(top_builddir)/src/Makefile.global + +TAP_TESTS = 1 + +ifeq ($(PORTNAME), win32) +LDFLAGS += -lws2_32 +endif + +override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) +LDFLAGS_INTERNAL += $(libpq_pgport) + +PROGS = test_stored_proc_outparams$(X) + +all: $(PROGS) + +check: $(PROGS) + $(prove_check) + +clean distclean maintainer-clean: + rm -f $(PROGS) *.o diff --git a/src/test/modules/test_libpq/README b/src/test/modules/test_libpq/README new file mode 100644 index 0000000000..468ab172a0 --- /dev/null +++ b/src/test/modules/test_libpq/README @@ -0,0 +1,4 @@ +src/test/modules/test_libpq + +This directory is for test programs using libpq, not via psql, and their +associated TAP tests. diff --git a/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.pl b/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.pl new file mode 100644 index 0000000000..5441084f40 --- /dev/null +++ b/src/test/modules/test_libpq/t/001_test_stored_proc_outparams.pl @@ -0,0 +1,105 @@ +use strict; +use warnings; + +use PostgresNode; +use TestLib; +use Test::More; + +my $node = get_new_node('main'); +$node->init; +$node->start; + +my @creation = ( + q{ + create procedure out_params_d(arg int, out textres text, out intres int) + language plpgsql as + $$ + begin + textres := 'foo'; + intres := arg * 5; + end; + $$}, + q{ + create procedure out_params_d(arg int, out boolres boolean, + out floatres float8) + language plpgsql as + $$ + begin + boolres := false; + floatres := arg * 8.0; + end; + $$}, + + q{ + create procedure out_params_uniq(arg int, out bres boolean, + out fres float8) + language plpgsql as + $$ + begin + bres := true; + fres := arg * 13.0; + end; + $$},); + + + +$node->safe_psql('postgres', $_) foreach (@creation); + +my $connstr = $node->connstr('postgres'); + +# query, test name, status, match pattern +my @tests = ( + [ + 'call out_params_uniq($1,$2,$3)', + 'unique - out param casts not needed', + 'ok', + qr{bres\|fres.*t\s*\|\s*26}s + ], + [ + 'call out_params_d($1,$2::text,$3)', + 'duplicate - text param', + 'ok', + qr{textres\|intres.*foo\s*\|\s*10}s + ], + [ + 'call out_params_d($1,$2::boolean,$3)', + 'duplicate - boolean param', + 'ok', + qr{boolres\|floatres.*f\s*\|\s*16}s + ], + [ + 'call no_such_proc($1,$2,$3)', + 'no such proc', + 'bad', + qr{ERROR:\s+procedure no_such_proc\(integer, unknown, unknown\) does not exist} + ], + [ + 'call out_params_d($1,$2,$3)', + 'duplicate - not enough casts', + 'bad', + qr{ERROR:\s+procedure out_params_d\(integer, unknown, unknown\) is not unique} + ],); + +# TAP tests are run from the source direectory, but the binary is found in +# the build directory. This is the simplest way to get at it + +my $exe = "$ENV{TESTDIR}/test_stored_proc_outparams"; + +foreach my $test (@tests) +{ + my ($query, $name, $type, $match) = @$test; + if ($type eq 'ok') + { + TestLib::command_like([ $exe, $connstr, $query ], $match, $name); + } + else + { + TestLib::command_fails_like([ $exe, $connstr, $query ], $match, + $name); + } +} + +done_testing(); + +$node->stop('fast'); + diff --git a/src/test/modules/test_libpq/test_stored_proc_outparams.c b/src/test/modules/test_libpq/test_stored_proc_outparams.c new file mode 100644 index 0000000000..441ce7ef86 --- /dev/null +++ b/src/test/modules/test_libpq/test_stored_proc_outparams.c @@ -0,0 +1,89 @@ +/* + * src/test/examples/testlibpq.c + * + * + * testlibpq.c + * + * Test the C version of libpq, the PostgreSQL frontend library. + */ +#include +#include +#include "libpq-fe.h" + +static void test_sp_out(PGconn *conn, const char *query); + +static void +exit_nicely(PGconn *conn) +{ + PQfinish(conn); + exit(1); +} + +int +main(int argc, char **argv) +{ + const char *conninfo; + const char *query; + PGconn *conn; + + /* required args: conninfo and query string */ + if (argc != 3) + { + fprintf(stderr, "usage: %s conninfo querystring\n", argv[0]); + exit(2); + } + conninfo = argv[1]; + query = argv[2]; + + /* Make a connection to the database */ + conn = PQconnectdb(conninfo); + + /* Check to see that the backend connection was successfully made */ + if (PQstatus(conn) != CONNECTION_OK) + { + fprintf(stderr, "Connection to database failed: %s", + PQerrorMessage(conn)); + exit_nicely(conn); + } + + test_sp_out(conn, query); + + /* close the connection to the database and cleanup */ + PQfinish(conn); + + return 0; +} + +#define INTOID 23 +#define UNKNOWNOID 705 + +void +test_sp_out(PGconn *conn, const char *query) +{ + /* + * We expect a call to a procedure with one IN param and 2 OUT params. The + * query needs to match this pattern for success. Casts are needed in the + * query (only) to disambiguate. The IN param here has a hardcoded value + * of 2. + */ + + PGresult *res; + const PQprintOpt opt = {1, 1, 0, 0, 0, 0, "|", NULL, NULL, NULL}; + int nParams = 3; + Oid paramTypes[3] = {INTOID, UNKNOWNOID, UNKNOWNOID}; + const char *paramValues[3] = {"2", NULL, NULL}; + int resultFormat = 0; + + res = PQexecParams(conn, query, nParams, paramTypes, paramValues, + NULL, NULL, resultFormat); + + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + fprintf(stderr, "CALL failed: %s", PQerrorMessage(conn)); + PQclear(res); + exit_nicely(conn); + } + + PQprint(stdout, res, &opt); + +} diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 211a42cefa..3838fa2324 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 89b96d580f..2ef1c82cea 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 --------------458FB4393C51720E98781C51--