Functions

Having explained the main concepts behind the PL/pgSQL language, it is now time to turn code snippets into reusable functions.

A function is an entity uniquely identified by a prototype, which is made up of the following:

• A (mnemonic) name
• An argument list
• A return type

PostgreSQL does allow function overloading, which refers to the ability to define several implementations of the same function with the same name. Overloaded functions must have a different prototype, but cannot have a different return type. This means that overloaded functions differ with regard to their argument list (the type and number of arguments).

Each function's prototype is expressed by the CREATE FUNCTION SQL statement, while the implementation of the function can be any supported code block, with plpgsql being the most common language. The declaration of the function must specify the language its implementation code block is expressed in, so that the executor knows how to run the function.

A typical template for declaring a function is as follows:

CREATE FUNCTION foo( arg1,  arg2, ...)
RETURNS int
AS <block of code>
LANGUAGE plpgsql;

The function name is specified immediately after the CREATE FUNCTION statement and the argument list is specified in parentheses. The return type value is specified with the RETURNS keyword, and the language in which the function is implemented is specified with the LANGUAGE keyword. The block of code that implements the function is specified after the AS clause, and is usually expressed as a dollar-quoted string. There are more options and properties to declare and define a function, which we will look at later on.

It is interesting to note that CREATE FUNCTION supports the OR REPLACE clause. This makes it very easy to override the current implementation of a function and is therefore a best practice to follow in day-to-day function management.

 CREATE OR REPLACE FUNCTION is pretty much equivalent to a DROP and CREATE FUNCTION sequence of commands, but CREATE OR REPLACE FUNCTION will only substitute the original version of the function if the new version does not contain errors and compiles successfully. It is therefore a safer approach because it will not delete the existing function until a new version can be used to replace it.

In order to throw away a function, the DROP FUNCTION statement can be used. The function must be uniquely identified, meaning its name and argument list must be specified. Specifying the return type is not important because overloaded functions cannot change this. If there is only one implementation of the function, meaning there is no overloading applied, it is sufficient to indicate just the function name:

    -- only if not overloaded
testdb=> DROP FUNCTION foo;
    -- trash only this among overloaded implementation of foo
testdb=> DROP FUNCTION foo( int, text );

A function is invoked via its name by putting arguments (if there are any) in surrounding parentheses. Each time a regular SQL statement holds a function reference, it will invoke it. The simplest form of function invocation is by means of a SELECT statement, as follows:

-- invokes the 'now' function  with no arguments
testdb=> SELECT now();

It is possible to see all the user-defined functions with the special psql command, \df (describe function). If no arguments are passed, then all functions will be shown, otherwise only functions with a name that matches the argument string will be shown:

testdb=> \df
                                               List of functions
 Schema |          Name          | Result data type |                Argument data types                | Type
--------+------------------------+------------------+---------------------------------------------------+------
 public | f_dump_function        | SETOF text       | function_name text                                | func
 public | f_fake_files           | SETOF files      | max integer DEFAULT 10                            | func
 public | f_file_by_type         | SETOF files      | file_type text DEFAULT 'txt'::text                | func
 public | f_files_from_directory | SETOF files      | dir text DEFAULT '.'::text                        | func
 public | f_files_tuples         | SETOF files      | fake boolean DEFAULT false, max integer DEFAULT 5 | func
 public | f_human_file_size      | text             | f_size numeric                                    | func

-- get only function with a name starting with 'f_files'
testdb=> \df f_files*
        List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------------------------+------------------+---------------------------------------------------+------
 public | f_files_from_directory | SETOF files | dir text DEFAULT '.'::text | func
 public | f_files_tuples | SETOF files | fake boolean DEFAULT false, max integer DEFAULT 5 | funcc

In the following sections, each property of a function will be detailed, with examples of increasing difficulty.