Functions

PostgreSQL SQL provides three types of functions:

Every kind of function can take a base type, a composite type, or some combination as arguments (parameters). In addition, all can return a base type or a composite type. It is easiest to define SQL functions, so we will start with those.

Query Language (SQL) Functions

SQL functions execute an arbitrary list of SQL queries, returning the results of the last query in the list. SQL functions in general return sets. If their returntype is not specified as a setof, then an arbitrary element of the last query's result is returned.

Here is the sytax of a typical SQL query:

CREATE FUNCTION fname (type [,type ...]) 
    RETURNS returntype
    AS 'SQL_commands'
LANGUAGE 'sql';

In the syntax example above, the SQL_commands following the AS should be a list of queries separated by semicolons and bracketed within single-quote marks. Note that quote marks used in the queries must be escaped, by preceding them with a backslash.

Arguments to the SQL function may be referenced in the queries using a $n syntax: $1 refers to the first argument, $2 to the second, and so on. If an argument is of a compatible type, then a dot notation (for example, "$1.emp") may be used to access attributes of the argument or to invoke functions.

SQL Functions on Composite Types

When specifying functions with arguments of composite types (such as EMP), we must not only specify which argument we want (as we did above with $1 and $2) but also the attributes of that argument. For example, take the function double_salary that computes what your salary would be if it were doubled:
CREATE FUNCTION double_salary(EMP) 
    RETURNS int4
    AS 'SELECT $1.salary * 2 AS salary;' 
    LANGUAGE 'sql';

SELECT name, double_salary(EMP) AS dream
    FROM EMP
    WHERE EMP.cubicle ~= point '(2,1)';


name | dream 
-----+-------
Sam  | 2400  

Notice the use of the syntax $1.salary. Before launching into the subject of functions that return composite types, we must first introduce the function notation for projecting attributes. The simple way to explain this is that we can usually use the notations attribute(table) and table.attribute interchangeably:
--
-- this is the same as:
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;

youngster 
----------
Sam       

As we shall see, however, this is not always the case. This function notation is important when we want to use a function that returns a single row. We do this by assembling the entire row within the function, attribute by attribute. This is an example of a function that returns a single EMP row:
CREATE FUNCTION new_emp() 
    RETURNS EMP
    AS 'SELECT text ''None'' AS name,
        1000 AS salary,
        25 AS age,
        point ''(2,2)'' AS cubicle'
    LANGUAGE 'sql';

In this case we have specified each of the attributes with a constant value, but any computation or expression could have been substituted for these constants. Defining a function like this can be tricky. Some of the more important caveats are as follows:

Any collection of commands in the SQL query language can be packaged together and defined as a function. The commands can include updates (that is, INSERT, UPDATE, and DELETE) as well as SELECT queries. However, the final command must be a SELECT that returns whatever is specified as the function's returntype.

CREATE FUNCTION clean_EMP () 
    RETURNS int4
    AS 'DELETE FROM EMP 
        WHERE EMP.salary <= 0;
        SELECT 1 AS ignore_this;'
    LANGUAGE 'sql';

SELECT clean_EMP();

clean_EMP
---------
        1 

Procedural Language Functions

Procedural languages are not built into PostgreSQL. They are offered by loadable modules. Please refer to the documentation for the PL in question for details about the syntax and how the AS clause is interpreted by the PL handler.

There are currently three procedural languages available in the standard PostgreSQL distribution (PL/pgSQL, PL/Tcl, and PL/Perl), and other languages can be defined. Refer to the Red Hat Database Programmer's Guide for more information.

Compiled (C) Language Functions

Functions written in C can be compiled into dynamically loadable objects (also called shared libraries), and used to implement user-defined SQL functions. The first time a user-defined function in a particular loadable object file is called in a backend session, the dynamic loader loads that object file into memory so that the function can be called. The CREATE FUNCTION for a user-defined function must therefore specify two pieces of information for the function: the name of the loadable object file, and the C name (link symbol) of the specific function to call within that object file. If the C name is not explicitly specified then it is assumed to be the same as the SQL function name.

Note

After it is used for the first time, a dynamically loaded user function is retained in memory, and future calls to the function in the same session will only incur the small overhead of a symbol table lookup.

The string that specifies the object file (the first string in the AS clause) should be the full path of the object code file for the function, bracketed by single quote marks. If a link symbol is given in the AS clause, the link symbol should also be bracketed by single quote marks, and should be exactly the same as the name of the function in the C source code. On Unix systems the command nm will print all of the link symbols in a dynamically loadable object.

Note

PostgreSQL will not compile a function automatically; it must be compiled before it is used in a CREATE FUNCTION command. See the Red Hat Database Programmer's Guide for additional information.

Internal Functions

Internal functions are functions written in C that have been statically linked into the PostgreSQL backend process. The AS clause gives the C-language name of the function, which need not be the same as the name being declared for SQL use. (For reasons of backwards compatibility, an empty AS string is accepted as meaning that the C-language function name is the same as the SQL name.) Normally, all internal functions present in the backend are declared as SQL functions during database initialization, but a user could use CREATE FUNCTION to create additional alias names for an internal function.

Internal functions are declared in CREATE FUNCTION with language name internal.