4.3. Calling Functions

4.3. Calling Functions
Prev	Up	Chapter 4. SQL Syntax	Home	Next

4.3.1. Using Positional Notation
4.3.2. Using Named Notation
4.3.3. Using Mixed Notation

+ 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. + For each notation, also consider the effect of function argument types, + documented in Section 10.3. +

+ 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 Chapter 37 for + more information). +

4.3.1. Using 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. +

4.3.2. Using Named Notation

+ In named notation, each argument's name is specified using + => to separate it from the argument expression. + For example: +

+SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
+ 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(a => 'Hello', b => 'World', uppercase => true);
+ concat_lower_or_upper 
+-----------------------
+ HELLO WORLD
+(1 row)
+
+SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
+ concat_lower_or_upper 
+-----------------------
+ HELLO WORLD
+(1 row)
+

+ An older syntax based on ":=" is supported for backward compatibility: +

+SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
+ concat_lower_or_upper 
+-----------------------
+ HELLO WORLD
+(1 row)
+

4.3.3. Using 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', uppercase => true);
+ 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. +

Note

+ Named and mixed call notations currently cannot be used when calling an + aggregate function (but they do work when an aggregate function is used + as a window function). +

Prev	Up	Next
4.2. Value Expressions	Home	Chapter 5. Data Definition