PolarDB:Calling functions

Notations at a glance

Example function

All examples on this page use the following function:

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;

concat_lower_or_upper takes two mandatory parameters (a and b) and one optional parameter (uppercase, which defaults to false). It concatenates a and b, then returns the result in uppercase or lowercase depending on uppercase.

Positional notation

Pass arguments in the same order as the function declaration. Optional parameters can only be omitted from right to left.

-- All three arguments supplied
SELECT concat_lower_or_upper('Hello', 'World', true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

-- Trailing optional argument omitted; uppercase defaults to false
SELECT concat_lower_or_upper('Hello', 'World');
 concat_lower_or_upper
-----------------------
 hello world
(1 row)

Positional notation only lets you omit arguments from the end of the parameter list. To skip a non-trailing optional parameter, use named notation instead.

Named notation

Use => to pair each argument with its parameter name. Arguments can appear in any order, and you can omit any optional parameter regardless of its position in the declaration.

-- Trailing optional argument omitted
SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
 concat_lower_or_upper
-----------------------
 hello world
(1 row)

-- All arguments in declaration order
SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

-- Arguments in a different order
SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

The := operator is also accepted for backward compatibility with older PostgreSQL versions. Prefer => in new code.

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

Mixed notation

Combine positional and named arguments in a single call. Positional arguments must come first; named arguments follow.

-- a and b are positional; uppercase is named
SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

Mixed notation is most useful when a function has several parameters with defaults. Pass the leading mandatory arguments positionally and use named notation only for the optional parameters you need to set.

Limitations

Named and mixed notation cannot be used when calling an aggregate function directly. They do work when an aggregate function is used as a window function.