CREATE FUNCTION

Name

CREATE FUNCTION  --  Defines a new function within the database.

Synopsis

CREATE FUNCTION name ( [ ftype [, ...] ] )
    RETURNS rtype
    AS definition
    LANGUAGE 'langname'
    [ WITH ( attribute [, ...] ) ]
CREATE FUNCTION name ( [ ftype [, ...] ] )
    RETURNS rtype
    AS obj_file , link_symbol
    LANGUAGE 'langname'
    [ WITH ( attribute [, ...] ) ]
  

Parameters

name

Use this parameter to supply the name you wish to use for the new function.

ftype

This parameter holds the definitions for any data types within the function's arguments. There are three input types you may use: base, complex types, or opaque. Opaque explicitly allows the function to accept arguments of invalid types. This type is used only by internal functions.

rtype

This parameter takes the new function's return data type; this may be set as a base type, complex type, setof type, or opaque type. Using the setof modifier states that the function will return multiple items (by default it is set to return only one item).

attribute

This parameter is optional; it can be used to add optimization information about the function.

definition

This parameter takes the definition of the function you are creating. The definition needs to be entered as a string, due to the wide variation of function definition types between different languages. Likewise, the exact content of this string is language-specific; it could be an internal function name, the path to an object file, an SQL query, or text in a procedural language.

obj_file , link_symbol

This parameter takes the shared object file name of a dynamically linked C language function which has a source code defined name that is different than the SQL function name. The obj_file is the file that contains the dynamically loadable object. The link_symbol is function name in the C language source code.

langname

This parameter takes the name of the function the new language will use. Possible options for this parameter are: C , sql, internal, or plname. The plname field specifies the name of a created procedural language. See CREATE LANGUAGE for further details.

Results

CREATE

This message is displayed if the function is created successfully.

Description

Use the CREATE FUNCTION command to create a function and record it within the database. Ownership of the function is set to the user account that created it.

Function Options

The possible options for the WITH clause are as follow:

iscachable

This option specifies that the function will always return the same result when passed the same argument values (hence the name). This process does not perform a database lookup or use information not directly present in the parameter list. This option is used by the optimizer to determine it is safe to pre-evaluate a function call.

isstrict

When you set this option, you are specfing that the function is strict; this means that whenever the function is passed a NULL argument it will return a NULL value and, essentially, will not execute.

Function Overloading

PostgreSQL allows what is known as function overloading. Users of programming languages such as C++ may be familiar with this term. Basically, what it means is that you are allowed to create multiple functions of the same name, as long as each of them has a unique set of internal arguments.

NoteC Functions
 

Unlike PostgreSQL, two internal C functions are unable to share the same name without causing errors and ceasing compilation of the code. To avoid this problem, you can include the argument type as part of the C function name. When the AS clause is not specified, CREATE FUNCTION assumes that the C function name is the same as the SQL name.

This is useful for creating (what seems to be) a single function that can handle a large variety of different input data; to the user, the series of functions you have created become a relatively seamless integration.

The best way to go about overloading SQL functions with multiple C functions would be to label the different functions with distinct names. Then, when creating the function with CREATE FUNCTION, use the alternate CREAT FUNCTION syntax to select the correct C functions for each overloaded SQL function.

Examples

The following example create a simple SQL function that returns a book title based on the ID number given it.

 CREATE FUNCTION title(integer) RETURNS text
     AS 'SELECT title from books where id = $1'
        LANGUAGE 'sql';

The next example will use the title function to retrieve rows with ID numbers matching the supplied number.

booktown=# SELECT title(41472) AS book_title;
      book_title
----------------------
 Practical PostgreSQL
(1 row)