From 717fa274d14d9cd25396b85bb92f567e1c623f0c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 8 Oct 2009 02:39:25 +0000 Subject: Support use of function argument names to identify which actual arguments match which function parameters. The syntax uses AS, for example funcname(value AS arg1, anothervalue AS arg2) Pavel Stehule --- doc/src/sgml/ref/create_function.sgml | 23 ++++- doc/src/sgml/sources.sgml | 4 +- doc/src/sgml/syntax.sgml | 171 +++++++++++++++++++++++++++++++++- doc/src/sgml/xfunc.sgml | 67 ++++++++++++- 4 files changed, 253 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 0843a175638..6d7eb84d8f2 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -65,7 +65,7 @@ CREATE [ OR REPLACE ] FUNCTION Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. (When using OUT - parameters, that means you cannot change the names or types of any + parameters, that means you cannot change the types of any OUT parameters except by dropping the function.) @@ -121,8 +121,11 @@ CREATE [ OR REPLACE ] FUNCTION The name of an argument. Some languages (currently only PL/pgSQL) let you use the name in the function body. For other languages the - name of an input argument is just extra documentation. But the name - of an output argument is significant, since it defines the column + name of an input argument is just extra documentation, so far as + the function itself is concerned; but you can use input argument names + when calling a function to improve readability (see ). In any case, the name + of an output argument is significant, because it defines the column name in the result row type. (If you omit the name for an output argument, the system will choose a default column name.) @@ -570,6 +573,18 @@ CREATE FUNCTION foo(int, int default 42) ... to replace it (this includes being a member of the owning role). + + When replacing an existing function with CREATE OR REPLACE + FUNCTION, there are restrictions on changing parameter names. + You cannot change the name already assigned to any input parameter + (although you can add names to parameters that had none before). + If there is more than one output parameter, you cannot change the + names of the output parameters, because that would change the + column names of the anonymous composite type that describes the + function's result. These restrictions are made to ensure that + existing calls of the function do not stop working when it is replaced. + + diff --git a/doc/src/sgml/sources.sgml b/doc/src/sgml/sources.sgml index 0872eacee76..342f8e4ef73 100644 --- a/doc/src/sgml/sources.sgml +++ b/doc/src/sgml/sources.sgml @@ -1,4 +1,4 @@ - + PostgreSQL Coding Conventions @@ -125,7 +125,7 @@ ereport(ERROR, (errcode(ERRCODE_AMBIGUOUS_FUNCTION), errmsg("function %s is not unique", func_signature_string(funcname, nargs, - actual_arg_types)), + NIL, actual_arg_types)), errhint("Unable to choose a best candidate function. " "You might need to add explicit typecasts."))); diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 73db3235bd6..20f7085a8d3 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ - + SQL Syntax @@ -1505,6 +1505,11 @@ sqrt(2) The list of built-in functions is in . Other functions can be added by the user. + + + The arguments can optionally have names attached. + See for details. + @@ -2123,4 +2128,168 @@ SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; + + Calling Functions + + + notation + functions + + + + PostgreSQL allows functions that have named + parameters to be called using either positional or + named notation. Named notation is especially + useful for functions that have a large number of parameters, since it + makes the associations between parameters and actual arguments more + explicit and reliable. + In positional notation, a function call is written with + its argument values in the same order as they are defined in the function + declaration. In named notation, the arguments are matched to the + function parameters by name and can be written in any order. + + + + In either notation, parameters that have default values given in the + function declaration need not be written in the call at all. But this + is particularly useful in named notation, since any combination of + parameters can be omitted; while in positional notation parameters can + only be omitted from right to left. + + + + PostgreSQL also supports + mixed notation, which combines positional and + named notation. In this case, positional parameters are written first + and named parameters appear after them. + + + + The following examples will illustrate the usage of all three + notations, using the following function definition: + +CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) +RETURNS text +AS +$$ + SELECT CASE + WHEN $3 THEN UPPER($1 || ' ' || $2) + ELSE LOWER($1 || ' ' || $2) + END; +$$ +LANGUAGE SQL IMMUTABLE STRICT; + + Function concat_lower_or_upper has two mandatory + parameters, a and b. Additionally + there is one optional parameter uppercase which defaults + to false. The a and + b inputs will be concatenated, and forced to either + upper or lower case depending on the uppercase + parameter. The remaining details of this function + definition are not important here (see for + more information). + + + + Using positional notation + + + function + positional notation + + + + Positional notation is the traditional mechanism for passing arguments + to functions in PostgreSQL. An example is: + +SELECT concat_lower_or_upper('Hello', 'World', true); + concat_lower_or_upper +----------------------- + HELLO WORLD +(1 row) + + All arguments are specified in order. The result is upper case since + uppercase is specified as true. + Another example is: + +SELECT concat_lower_or_upper('Hello', 'World'); + concat_lower_or_upper +----------------------- + hello world +(1 row) + + Here, the uppercase parameter is omitted, so it + receives its default value of false, resulting in + lower case output. In positional notation, arguments can be omitted + from right to left so long as they have defaults. + + + + + Using named notation + + + function + named notation + + + + In named notation, each argument's name is specified using the + AS keyword. For example: + +SELECT concat_lower_or_upper('Hello' AS a, 'World' AS b); + concat_lower_or_upper +----------------------- + hello world +(1 row) + + Again, the argument uppercase was omitted + so it is set to false implicitly. One advantage of + using named notation is that the arguments may be specified in any + order, for example: + +SELECT concat_lower_or_upper('Hello' AS a, 'World' AS b, true AS uppercase); + concat_lower_or_upper +----------------------- + HELLO WORLD +(1 row) + +SELECT concat_lower_or_upper('Hello' AS a, true AS uppercase, 'World' AS b); + concat_lower_or_upper +----------------------- + HELLO WORLD +(1 row) + + + + + + Using mixed notation + + + function + mixed notation + + + + The mixed notation combines positional and named notation. However, as + already mentioned, named arguments cannot precede positional arguments. + For example: + +SELECT concat_lower_or_upper('Hello', 'World', true AS uppercase); + concat_lower_or_upper +----------------------- + HELLO WORLD +(1 row) + + In the above query, the arguments a and + b are specified positionally, while + uppercase is specified by name. In this example, + that adds little except documentation. With a more complex function + having numerous parameters that have default values, named or mixed + notation can save a great deal of writing and reduce chances for error. + + + + diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 6d85d2d2629..1c20f15226a 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,4 +1,4 @@ - + User-Defined Functions @@ -517,6 +517,39 @@ SELECT getname(new_emp()); + + <acronym>SQL</> Functions with Parameter Names + + + function + named parameter + + + + It is possible to attach names to a function's parameters, for example + + +CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$ + UPDATE bank + SET balance = balance - $2 + WHERE accountno = $1 + RETURNING balance; +$$ LANGUAGE SQL; + + + Here the first parameter has been given the name acct_no, + and the second parameter the name debit. + So far as the SQL function itself is concerned, these names are just + decoration; you must still refer to the parameters as $1, + $2, etc within the function body. (Some procedural + languages let you use the parameter names instead.) However, + attaching names to the parameters is useful for documentation purposes. + When a function has many parameters, it is also useful to use the names + while calling the function, as described in + . + + + <acronym>SQL</> Functions with Output Parameters @@ -571,7 +604,10 @@ LANGUAGE SQL; but not having to bother with the separate composite type definition - is often handy. + is often handy. Notice that the names attached to the output parameters + are not just decoration, but determine the column names of the anonymous + composite type. (If you omit a name for an output parameter, the + system will choose a name on its own.) @@ -621,7 +657,7 @@ DROP FUNCTION sum_n_product (int, int); must be declared as being of an array type. For example: -CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$ +CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; @@ -661,6 +697,25 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]); normally. VARIADIC can only be attached to the last actual argument of a function call. + + + The array element parameters generated from a variadic parameter are + treated as not having any names of their own. This means it is not + possible to call a variadic function using named arguments (), except when you specify + VARIADIC. For example, this will work: + + +SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4] AS arr); + + + but not these: + + +SELECT mleast(10 AS arr); +SELECT mleast(ARRAY[10, -1, 5, 4.4] AS arr); + + @@ -677,7 +732,9 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]); called with insufficiently many actual arguments. Since arguments can only be omitted from the end of the actual argument list, all parameters after a parameter with a default value have to have - default values as well. + default values as well. (Although the use of named argument notation + could allow this restriction to be relaxed, it's still enforced so that + positional argument notation works sensibly.) @@ -712,7 +769,7 @@ SELECT foo(); -- fails since there is no default for the first argument ERROR: function foo() does not exist The = sign can also be used in place of the - key word DEFAULT, + key word DEFAULT. -- cgit v1.2.3