PostgreSQL users have the option of extending the set of functions and operators available. If you have a common SQL or programmatic routine, custom functions can be an effective way to more succinctly and efficiently accomplish your tasks. Likewise, custom operators can be created to call these functions (or existing built-in functions) in order to make more efficient and legible SQL statements.
Functions and operators each exist as database objects, and are thus tied to a specific
database. Creating a function while connected to the
booktown database, for
example, creates a function object available only to users connected to
If you intend to re-use some general functions or operators in multiple databases, you
should create them in the
template1 database. This will clone the function
and operator objects from
template1 when a new database is created.
The following sections cover the creation, use, and removal of custom functions and operators.
Creating New Functions
PostgreSQL supports a variation of the SQL99
CREATE FUNCTION command.
It is not directly compatible with the standard, but it does allow for a variety of means to
extend PostgreSQL by creating your own customized functions (see Chapter 5, for more on functions in general).
Here is the syntax for
CREATE FUNCTION name ( [ argumenttype [, ...] ] ) RETURNS returntype AS 'definition' LANGUAGE 'languagename' [ WITH ( attribute [, ...] ) ]
[, ...] ...