Function argument list

A function declares its argument list by specifying at least one argument type in a comma-separated list wrapped in parentheses following the function name. Each parameter has the following:

• A mandatory SQL type (or a user-defined type)
• An optional name
• An optional default value, which is used when the function is invoked without a value for the parameter
• An optional direction, which is used when the parameter could also be a return value

If the function does not require any arguments, the list will be empty, resulting in a couple of empty parentheses. If no name is specified for an argument, it will be available through the positional aliases $1, $2, and so on. As a simple example, consider Listing 1, where three different arguments are specified for f_args. Since they have no name, the function accesses them via the $1, $2, and $3 positionals. The pg_typeof() internal function provides information about the type of each argument:

testdb=> CREATE OR REPLACE FUNCTION
f_args( int, float, text )
RETURNS void AS $code$ BEGIN
 RAISE INFO '$1 (type %) = %', pg_typeof( $1 ), $1;
 RAISE INFO '$2 (type %) = %', pg_typeof( $2 ), $2;
 RAISE INFO '$3 (type %) = %', pg_typeof( $3 ), $3;
 END $code$
LANGUAGE plpgsql;

testdb=> SELECT f_args( 10, 1.2, 'hello world' );
INFO:  $1 (type integer) = 10
INFO:  $2 (type double precision) = 1.2
INFO:  $3 (type text) = hello world

It is worth noting that the $ positionals are always available and it is possible to mix prototypes where a few parameters get an explicit name and others do not. This is shown in Listing 2, where the first and last variables are explicitly named a and b respectively and the middle argument is accessed via the $2 positional:

testdb=> CREATE OR REPLACE FUNCTION
f_args( a int,  float, b text )
RETURNS void AS $code$
  BEGIN
    RAISE INFO 'a (type %) = %', pg_typeof( a ), a;
    RAISE INFO '$2(type %) = %', pg_typeof( $2 ), $2;
    RAISE INFO 'b (type %) = %', pg_typeof( b ), b;
   END $code$
LANGUAGE plpgsql;

testdb=> SELECT f_args( 10, 1.2, 'hello world' );
INFO:  a (type integer) = 10
INFO:  $2(type double precision) = 1.2
INFO:  b (type text) = hello world

In practice, this means that giving a name to a variable within the function prototypes results in the following:

CREATE OR REPLACE FUNCTION f_args( a int, real, b text )
RETURNS void AS $code$
  DECLARE
     a ALIAS FOR $1;
     b ALIAS FOR $3;
  BEGIN
    ....

The preceding is a legal code and is similar to the pattern often used in Perl 5 functions and arguments.

Arguments can have default values, which are values that are used when the function is invoked without a value for a particular argument. Default values are specified within the function prototype using the DEFAULT keyword followed by a legal value for that particular argument. As an example, Listing 42 defines a default value for each argument and shows how they are used in different invocations.

testdb=> CREATE OR REPLACE FUNCTION
f_args( a int DEFAULT 10, float DEFAULT 3.14,
        b text DEFAULT 'Hello Function World!' )
RETURNS void AS $code$
  BEGIN
    RAISE INFO 'a (type %) = %', pg_typeof( a ), a;
    RAISE INFO '$2(type %) = %', pg_typeof( $2 ), $2;
    RAISE INFO 'b (type %) = %', pg_typeof( b ), b;
   END $code$ LANGUAGE plpgsql;

-- all default values
testdb=> SELECT f_args();
INFO:  a (type integer) = 10
INFO:  $2(type double precision) = 3.14
INFO:  b (type text) = Hello Function World!

-- two default values
testdb=> SELECT f_args( 123 );
INFO:  a (type integer) = 123
INFO:  $2(type double precision) = 3.14
INFO:  b (type text) = Hello Function World!

-- no default values
testdb=> SELECT f_args( 123, 1.5, 'Mewl' );
INFO:  a (type integer) = 123
INFO:  $2(type double precision) = 1.5
INFO:  b (type text) = Mewl

-- DEFAULT does not triggers when a NULL value
-- is passed!
testdb=> SELECT f_args( 123, NULL, NULL );
INFO:  a (type integer) = 123
INFO:  $2(type double precision) = <NULL>
INFO:  b (type text) = <NULL>

As the last invocation in Listing 3 shows, a default value is applied only when no value at all is specified for an argument. This means that passing NULL does not trigger the default value for that argument; rather it assigns NULL to the argument.

An argument can have an optional direction, which can be any of the following:

• IN: This is the default. It means that the value is passed as an argument from the caller to the function
• OUT: This means the argument will be passed from the function to the caller
• INOUT: This means the argument will be first passed from the caller to the function and then back from the function to the caller

There are some rules to follow when using parameter directions:

• Only IN arguments can have DEFAULT values
• OUT arguments cannot be passed to functions
• If there is only one OUT or INOUT argument, the return type of the function must match that argument type; otherwise, a record type must be used

The example in Listing 4 shows a modification to f_args() so that it has two output arguments. Note that since the return type of the function is now a record, it is possible to manage the function as a table and perform a SELECT FROM. As you can see, the $2 and b arguments are those that are returned by the function execution, but since the $2 argument does not have an explicit name, it is shown as a generic column1 in the result set, while b keeps its original name:

testdb=> CREATE OR REPLACE FUNCTION
f_args( a IN int, INOUT float, b OUT text )
RETURNS record AS $code$
 BEGIN
 b := 'The real value was ' || $2;
 $2 := a * $2;
 END $code$ LANGUAGE plpgsql;

testdb=> SELECT * FROM f_args( 10, 5 );
 column1 | b
---------+-----------------------
 50 | The real value was 5

Of course, another way to pass values to the caller is by means of returning the values, as detailed in the next section. 

It is the responsibility of the function to check the input values and manage NULL values. However, it is possible to specify STRICT as a function property, which will avoid the execution of a function with a NULL input in its argument list, replacing the return value with a NULL value directly. Consider the simple function shown in Listing 5. It accepts two arguments, prints out a message with its argument values, and returns a string that concatenates the values:

testdb=> CREATE OR REPLACE FUNCTION
f_nullable( a int, b text )
RETURNS text AS $code$
  BEGIN
    RAISE INFO 'Invoked with [%] and [%]', a, b;
    RETURN a || b;
  END $code$ LANGUAGE plpgsql STRICT;

testdb=> SELECT f_nullable( 10, 'Ten' ) IS NULL;
INFO:  Invoked with [10] and [Ten]
 ?column?
----------
 f
testdb=> SELECT f_nullable( 10, NULL ) IS NULL;
 ?column?
----------
 t
testdb=> SELECT f_nullable( NULL, 'Ten' ) IS NULL;
 ?column?
----------
 t

testdb=> SELECT f_nullable( NULL, NULL ) IS NULL;
 ?column?
----------
 t

If the function is invoked with not-null arguments, it is effectively executed (the RAISE message is shown). When any of the two arguments are NULL, the function is not executed at all (no RAISE message appears) and the output of the function is immediately substituted with a NULL value. This option represents a performance boost (the function is not executed at all on NULL input) as well as a handy way of clearly marking a NULL output on a NULL input.

It is possible to declare a function so that it does not know exactly how many parameters it will receive on each invocation. These functions are commonly named variadic. In PL/pgSQL, the last parameter in an argument list can be marked with the VARIADIC special keyword, meaning that this argument will consume all extra arguments. There is, however, a constraint: all extra arguments must be of the same type and will be inserted in an array.

Let's consider the function in Listing 6. This accepts a single argument called separator and a variable list of tag names. As an output, it produces a single string with the hierarchy of the tags. Since it does not know how many tags will be passed to the function, the tags argument is marked as a VARIADIC text[] array, and will consume every extra argument. The implementation of the function is straightforward: it iterates over the tags array and concatenates the current tag string, the separator, and the current tag within the array in a giant string, returning it once no more array elements remain:

testdb=> CREATE OR REPLACE FUNCTION
f_tags_to_string( separator text, VARIADIC tags text[] )
RETURNS text AS $code$
 DECLARE
 tag_string text;
 current_tag text;
 BEGIN
 FOREACH current_tag IN ARRAY tags LOOP
 IF tag_string IS NULL THEN
 -- first assignment
 tag_string := current_tag;
 CONTINUE;
 END IF;

 tag_string := format( '%s %s %s', tag_string, separator, current_tag );
 END LOOP;
 RETURN tag_string;
 END $code$ LANGUAGE plpgsql;

testdb=> SELECT f_tags_to_string( '>>', 'favourites', 'music', 'rock' );
      f_tags_to_string
-----------------------------
 favourites >> music >> rock

testdb=> SELECT f_tags_to_string( '~', 'work', 'travel', 'conferences', 'rome', '2018' );
             f_tags_to_string
-------------------------------------------
 work ~ travel ~ conferences ~ rome ~ 2018

Variadic arguments impose several constraints on the function:

• The array must handle variables of the same type, as shown.
• The variadic argument must be the last one in the function declaration.
• This also implies that the arguments before the variadic one cannot have default values.
• If the function is declared as STRICT (meaning it returns NULL on a NULL input), the argument array is managed as a whole. This means that the whole array must be NULL.
• It is not possible to pass an already built array as a variadic argument unless it is explicitly marked as VARIADIC at the time the function is invoked.

With regard to the latter point, if the f_tags_to_string() function is invoked with an array of tags, the executor will not find any executable function, producing an error:

testdb=> SELECT f_tags_to_string( '~', ARRAY[ 'work', 'travel', 'conferences', 'rome', '2018' ] );
ERROR:  function f_tags_to_string(unknown, text[]) does not exist

To solve the problem, the VARIADIC keyword must be used at the time the function is invoked:

testdb=> SELECT f_tags_to_string( '~',VARIADIC ARRAY[ 'work', 'travel', 'conferences', 'rome', '2018' ] );
 f_tags_to_string
-------------------------------------------
 work ~ travel ~ conferences ~ rome ~ 2018