From e56bce5d43789cce95d099554ae9593ada92b3b7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 10 Jun 2021 17:11:36 -0400 Subject: Reconsider the handling of procedure OUT parameters. Commit 2453ea142 redefined pg_proc.proargtypes to include the types of OUT parameters, for procedures only. While that had some advantages for implementing the SQL-spec behavior of DROP PROCEDURE, it was pretty disastrous from a number of other perspectives. Notably, since the primary key of pg_proc is name + proargtypes, this made it possible to have multiple procedures with identical names + input arguments and differing output argument types. That would make it impossible to call any one of the procedures by writing just NULL (or "?", or any other data-type-free notation) for the output argument(s). The change also seems likely to cause grave confusion for client applications that examine pg_proc and expect the traditional definition of proargtypes. Hence, revert the definition of proargtypes to what it was, and undo a number of complications that had been added to support that. To support the SQL-spec behavior of DROP PROCEDURE, when there are no argmode markers in the command's parameter list, we perform the lookup both ways (that is, matching against both proargtypes and proallargtypes), succeeding if we get just one unique match. In principle this could result in ambiguous-function failures that would not happen when using only one of the two rules. However, overloading of procedure names is thought to be a pretty rare usage, so this shouldn't cause many problems in practice. Postgres-specific code such as pg_dump can defend against any possibility of such failures by being careful to specify argmodes for all procedure arguments. This also fixes a few other bugs in the area of CALL statements with named parameters, and improves the documentation a little. catversion bump forced because the representation of procedures with OUT arguments changes. Discussion: https://postgr.es/m/3742981.1621533210@sss.pgh.pa.us --- doc/src/sgml/catalogs.sgml | 5 +- doc/src/sgml/plpgsql.sgml | 17 +++++-- doc/src/sgml/ref/alter_extension.sgml | 11 ++--- doc/src/sgml/ref/alter_procedure.sgml | 4 +- doc/src/sgml/ref/call.sgml | 26 ++++++++-- doc/src/sgml/ref/comment.sgml | 11 ++--- doc/src/sgml/ref/drop_procedure.sgml | 92 ++++++++++++++++++++++++++++++----- doc/src/sgml/ref/drop_routine.sgml | 42 +++++++++++++--- doc/src/sgml/ref/security_label.sgml | 11 ++--- doc/src/sgml/xfunc.sgml | 22 ++++----- 10 files changed, 182 insertions(+), 59 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 16493209c63..f517a7d4aff 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -5905,9 +5905,8 @@ SCRAM-SHA-256$<iteration count>:&l An array of the data types of the function arguments. This includes only input arguments (including INOUT and - VARIADIC arguments), as well as - OUT parameters of procedures, and thus represents - the call signature of the function or procedure. + VARIADIC arguments), and thus represents + the call signature of the function. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index c97344ff927..a3edde35039 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -480,7 +480,7 @@ $$ LANGUAGE plpgsql; To call a function with OUT parameters, omit the - output parameter in the function call: + output parameter(s) in the function call: SELECT sales_tax(100.00); @@ -523,16 +523,20 @@ $$ LANGUAGE plpgsql; In a call to a procedure, all the parameters must be specified. For - output parameters, NULL may be specified. + output parameters, NULL may be specified when + calling the procedure from plain SQL: 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. + + However, when calling a procedure + from PL/pgSQL, you should instead write a + variable for any output parameter; the variable will receive the result + of the call. See + for details. @@ -2030,6 +2034,9 @@ BEGIN END; $$; + The variable corresponding to an output parameter can be a simple + variable or a field of a composite-type variable. Currently, + it cannot be an element of an array. diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index 38fd60128b7..c819c7bb4e3 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -212,12 +212,11 @@ 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 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. + 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. diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index 9cbe2c7ceaf..033fda92ee5 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -96,7 +96,7 @@ ALTER PROCEDURE name [ ( [ [ The data type(s) of the procedure's arguments (optionally schema-qualified), if any. + See for the details of how + the procedure is looked up using the argument data type(s). diff --git a/doc/src/sgml/ref/call.sgml b/doc/src/sgml/ref/call.sgml index abaa81c78b9..9e83a77b7c9 100644 --- a/doc/src/sgml/ref/call.sgml +++ b/doc/src/sgml/ref/call.sgml @@ -55,9 +55,24 @@ CALL name ( [ argument - An input argument for the procedure call. - See for the full details on - function and procedure call syntax, including use of named parameters. + An argument expression for the procedure call. + + + + Arguments can include parameter names, using the syntax + name => value. + This works the same as in ordinary function calls; see + for details. + + + + Arguments must be supplied for all procedure parameters that lack + defaults, including OUT parameters. However, + arguments matching OUT parameters are not evaluated, + so it's customary to just write NULL for them. + (Writing something else for an OUT parameter + might cause compatibility problems with + future PostgreSQL versions.) @@ -101,7 +116,10 @@ CALL do_db_maintenance(); Compatibility - CALL conforms to the SQL standard. + CALL conforms to the SQL standard, + except for the handling of output parameters. The standard + says that users should write variables to receive the values + of output parameters. diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 4f30bb93e2a..e07fc47fd31 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -176,12 +176,11 @@ COMMENT ON argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. - Note that COMMENT 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. + Note that COMMENT 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. diff --git a/doc/src/sgml/ref/drop_procedure.sgml b/doc/src/sgml/ref/drop_procedure.sgml index bf2c6ce1aaa..4c86062f343 100644 --- a/doc/src/sgml/ref/drop_procedure.sgml +++ b/doc/src/sgml/ref/drop_procedure.sgml @@ -30,10 +30,10 @@ DROP PROCEDURE [ IF EXISTS ] name [ Description - DROP PROCEDURE removes the definition of an existing - procedure. To execute this command the user must be the - owner of the procedure. The argument types to the - procedure must be specified, since several different procedures + DROP PROCEDURE removes the definition of one or more + existing procedures. To execute this command the user must be the + owner of the procedure(s). The argument types to the + procedure(s) usually must be specified, since several different procedures can exist with the same name and different argument lists. @@ -56,8 +56,7 @@ DROP PROCEDURE [ IF EXISTS ] name [ name - The name (optionally schema-qualified) of an existing procedure. If no - argument list is specified, the name must be unique in its schema. + The name (optionally schema-qualified) of an existing procedure. @@ -69,7 +68,7 @@ DROP PROCEDURE [ IF EXISTS ] name [ The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, - the default is IN. + the default is IN (but see below). @@ -82,7 +81,7 @@ DROP PROCEDURE [ IF EXISTS ] name [ The name of an argument. Note that DROP PROCEDURE does not actually pay any attention to argument names, since only the argument data - types are needed to determine the procedure's identity. + types are used to determine the procedure's identity. @@ -94,6 +93,7 @@ DROP PROCEDURE [ IF EXISTS ] name [ The data type(s) of the procedure's arguments (optionally schema-qualified), if any. + See below for details. @@ -121,12 +121,81 @@ DROP PROCEDURE [ IF EXISTS ] name [ + + Notes + + + If there is only one procedure of the given name, the argument list + can be omitted. Omit the parentheses too in this case. + + + + In PostgreSQL, it's sufficient to list the + input (including INOUT) arguments, + because no two routines of the same name are allowed to share the same + input-argument list. Moreover, the DROP command + will not actually check that you wrote the types + of OUT arguments correctly; so any arguments that + are explicitly marked OUT are just noise. But + writing them is recommendable for consistency with the + corresponding CREATE command. + + + + For compatibility with the SQL standard, it is also allowed to write + all the argument data types (including those of OUT + arguments) without + any argmode markers. + When this is done, the types of the procedure's OUT + argument(s) will be verified against the command. + This provision creates an ambiguity, in that when the argument list + contains no argmode + markers, it's unclear which rule is intended. + The DROP command will attempt the lookup both ways, + and will throw an error if two different procedures are found. + To avoid the risk of such ambiguity, it's recommendable to + write IN markers explicitly rather than letting them + be defaulted, thus forcing the + traditional PostgreSQL interpretation to be + used. + + + + The lookup rules just explained are also used by other commands that + act on existing procedures, such as ALTER PROCEDURE + and COMMENT ON PROCEDURE. + + + Examples + + If there is only one procedure do_db_maintenance, + this command is sufficient to drop it: + +DROP PROCEDURE do_db_maintenance; + + + + + Given this procedure definition: + +CREATE PROCEDURE do_db_maintenance(IN target_schema text, OUT results text) ... + + any one of these commands would work to drop it: -DROP PROCEDURE do_db_maintenance(); +DROP PROCEDURE do_db_maintenance(IN target_schema text, OUT results text); +DROP PROCEDURE do_db_maintenance(IN text, OUT text); +DROP PROCEDURE do_db_maintenance(IN text); +DROP PROCEDURE do_db_maintenance(text); +DROP PROCEDURE do_db_maintenance(text, text); -- potentially ambiguous + However, the last example would be ambiguous if there is also, say, + +CREATE PROCEDURE do_db_maintenance(IN target_schema text, IN options text) ... + + @@ -140,10 +209,11 @@ DROP PROCEDURE do_db_maintenance(); The standard only allows one procedure to be dropped per command. - The IF EXISTS option + The IF EXISTS option is an extension. - The ability to specify argument modes and names + The ability to specify argument modes and names is an + extension, and the lookup rules differ when modes are given. diff --git a/doc/src/sgml/ref/drop_routine.sgml b/doc/src/sgml/ref/drop_routine.sgml index 6c50eb44a19..0a0a140ba0f 100644 --- a/doc/src/sgml/ref/drop_routine.sgml +++ b/doc/src/sgml/ref/drop_routine.sgml @@ -30,15 +30,44 @@ DROP ROUTINE [ IF EXISTS ] name [ ( Description - DROP ROUTINE removes the definition of an existing - routine, which can be an aggregate function, a normal function, or a - procedure. See + DROP ROUTINE removes the definition of one or more + existing routines. The term routine includes + aggregate functions, normal functions, and procedures. See under , , and for the description of the parameters, more examples, and further details. + + Notes + + + The lookup rules used by DROP ROUTINE are + fundamentally the same as for DROP PROCEDURE; in + particular, DROP ROUTINE shares that command's + behavior of considering an argument list that has + no argmode markers to be + possibly using the SQL standard's definition that OUT + arguments are included in the list. (DROP AGGREGATE + and DROP FUNCTION do not do that.) + + + + In some cases where the same name is shared by routines of different + kinds, it is possible for DROP ROUTINE to fail with + an ambiguity error when a more specific command (DROP + FUNCTION, etc.) would work. Specifying the argument type + list more carefully will also resolve such problems. + + + + These lookup rules are also used by other commands that + act on existing routines, such as ALTER ROUTINE + and COMMENT ON ROUTINE. + + + Examples @@ -64,13 +93,14 @@ DROP ROUTINE foo(integer); The standard only allows one routine to be dropped per command. - The IF EXISTS option + The IF EXISTS option is an extension. - The ability to specify argument modes and names + The ability to specify argument modes and names is an + extension, and the lookup rules differ when modes are given. - Aggregate functions are an extension. + User-definable aggregate functions are an extension. diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index 407a09720b8..20a839ff0c3 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -126,12 +126,11 @@ 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 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. + 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. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 41bcc5b79dd..3771401c01d 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -765,7 +765,7 @@ DROP FUNCTION sum_n_product (int, int); parameter serves as both an input parameter (part of the calling argument list) and an output parameter (part of the result record type). VARIADIC parameters are input parameters, but are treated - specially as described next. + specially as described below. @@ -779,12 +779,8 @@ DROP FUNCTION sum_n_product (int, int); 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. - - - + differently from functions. In CALL commands, + output parameters must be included in the argument list. For example, the bank account debiting routine from earlier could be written like this: @@ -795,17 +791,21 @@ CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) 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 + To call this procedure, an argument matching the OUT + parameter must be included. It's customary to write NULL: CALL tp1(17, 100.0, NULL); + If you write something else, it must be an expression that is implicitly + coercible to the declared type of the parameter, just as for input + parameters. Note however that such an expression will not be evaluated. - Procedures with output parameters are more useful in PL/pgSQL, where the - output parameters can be assigned to variables. See PL/pgSQL, + instead of writing NULL you must write a variable + that will receive the procedure's output. See for details. -- cgit v1.2.3