Practical PostgreSQL

Extending PostgreSQL

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 booktown.

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:

CREATE FUNCTION name ( [ argumenttype [, ...] ] )
                RETURNS returntype
                AS 'definition'
                LANGUAGE 'languagename'
                [ WITH ( attribute [, ...] ) ]

CREATE FUNCTION name ( [ argumenttype [, ...