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 [, ...] ) ]
Use this parameter to supply the name you wish to use for the new function.
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.
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).
This parameter is optional; it can be used to add optimization information about the function.
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.
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.
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.
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.
The possible options for the WITH clause are as follow:
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.
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.
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.
C 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.
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)