Using functions, procedures, and routines

PostgreSQL 11 provides three main terms to refer to an executable unit of code: functions, procedures, and routines.

A function is what is often called a stored procedure and has always existed in PostgreSQL. A function, generally speaking, is a block of code associated with a mnemonic name. Functions can accept arguments and return a result after they have been executed. Both their arguments and their return values can be either scalar types (such as singles) or complex types (such as tuples). PostgreSQL exploits functions all over the cluster and can be used in queries and statements, to implement trigger behavior and, under particular circumstances, to build indexes.

Functions cannot interact with the transaction layer, which means they execute within the transaction of the invoking statement. Functions can be categorized by their implementation type:

• Normal functions: This usually refers to stored procedures
• Aggregate functions: These operate on a set of tuples and provide an aggregate single result (such as sum())
• Window functions: These perform computations over a set of tuples without reporting a single result (such as rank())

In this chapter, we will learn how to write normal functions, which are the most common ones.

A procedure is a new object introduced with PostgreSQL 11. In short, it is a special function that is able to interact with the transaction layer by committing a part of the work. Even if functions and procedures share several properties, procedures are quite different.

A routine can be either of the aforementioned two kinds of executable code. There is no object of routine type, rather routine is a shorthand to manage either a function or a procedure by either changing it or dropping it. To a certain extent, a routine is a synonym of either a function or a procedure.

Functions and procedures share definition attributes and properties, the most interesting of which is the ability to implement the executable code in a lot of different languages. In fact, in PostgreSQL, the syntax of the definition of either a procedure or a function has two parts:

• Declaration: This is the definition of the executable code, providing attributes such as the name, the arguments list, and the return types
• Implementation: This is the code that will be executed once the function or the procedure is invoked

The declaration is always expressed by an SQL statement, such as CREATE FUNCTION or CREATE PROCEDURE for a function or a procedure respectively. The implementation can be written in any supported language, meaning we can develop code in non-SQL languages such as Java, Perl, Python, and Ruby. The server is in charge of executing the code with the appropriate technology (such as a virtual machine), marshalling parameters, and return values.