PostgreSQL Call function

PostgreSQL allows functions to have named parameters that can be called by location or by name notation. Name notation is especially useful for functions with a large number of parameters, because it is more explicit and reliable to mark the connection between formal parameters and arguments. In positional notation, the parameter values of a function call are written in the same order as the function declaration. In the name notation, parameters are matched by name to function parameters and can be written out in any order.

Regardless of the notation, the parameters given at the time of the function declaration do not have to be written out at the time of invocation. However, this is particularly useful in name notation, because any combination of parameters can be omitted. In positional notation, parameters can only be omitted from right to left.

PostgreSQL also supports mixed notation, and mixed representations combine location and name notation. For this reason, the positional parameter is written and then the named parameter is followed.

The following example illustrates the use of three representations using the following function definition:

CREATE FUNCTION Concat_lower_or_upper (a text, b text, uppercase Boolean DEFAULT false) RETURNS textas$$ SELECT case        WHE N $ then UPPER ($ | | "| | $)        ELSE LOWER ($ | | "| | $)        end;$ $LANGUAGE SQL immutable STRICT;

concat_lower_or_upperThe function has two mandatory arguments,a and b. In addition, the third parameter is an optional parameter, uppercase, which defaults to false. a and b inputs will be concatenated and will be forced to uppercase or lowercase according to the uppercase parameter.

1. Using position notation

In PostgreSQL, positional notation is the traditional mechanism for passing parameters to functions. An example is:

SELECT concat_lower_or_upper (' Hello ', ' world ', true); Concat_lower_or_upper-----------------------HELLO World (1 row)

All parameters are specified in order. Because uppercase is specified as true, the result is uppercase. Another example is:

SELECT concat_lower_or_upper (' Hello ', ' world '); Concat_lower_or_upper-----------------------Hello World (1 row)

Here, the parameter uppercaseis omitted, so accept its default value false, resulting in lowercase output. In positional notation, parameters can be omitted from right to left as long as they have a default value.

2. Using the name notation

In name notation, each parameter name is declared using : = , which is used 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)

In addition, the parameter uppercase is omitted, so it is implicitly set to false. One of the benefits of using name notation is that parameters can be declared 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)

3. Using mixed notation

The mixed notation combines position and name notation. However, as mentioned earlier, the named parameter cannot precede the position parameter. 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 uppercase are declared with a location, and uppercase is declared with a name. In this example, you add a point that is not in the document. In a more complex function with more than one parameter with default values, the name or the mixed notation can save a lot of keystrokes and reduce the chance of making mistakes.

Note: when calling an aggregate function, it is not possible to use names and mixed call notation at this time (but they do work when aggregate functions are used as window functions).

More knowledge at Http://www.infocool.net

PostgreSQL Call function