CREATE FUNCTION SQL - Language Statements CREATE FUNCTION Defines a new function 1998-09-09 CREATE FUNCTION name ( [ ftype [, ...] ] ) RETURNS rtype AS definition LANGUAGE 'langname' 1998-09-09 name The name of a function to create. ftype The data type of function arguments. rtype The return data type. definition A string defining the function; the meaning depends on the language. It may be an internal function name, the path to an object file, an SQL query, or text in a procedural language. langname may be 'C', 'sql', 'internal' or 'plname', where 'plname' is the name of a created procedural language. See CREATE LANGUAGE for details. 1998-09-09 CREATE This is returned if the command completes successfully. 1998-09-09 CREATE FUNCTION allows a Postgres user to register a function with a database. Subsequently, this user is treated as the owner of the function. 1998-09-09 Refer to the chapter on functions in the PostgreSQL Programmer's Guide for further information. Use DROP FUNCTION to drop user-defined functions. Postgres allows function "overloading"; that is, the same name can be used for several different functions so long as they have distinct argument types. This facility must be used with caution for INTERNAL and C-language functions, however. Two INTERNAL functions cannot have the same C name without causing errors at link time. To get around that, give them different C names (for example, use the argument types as part of the C names), then specify those names in the AS clause of CREATE FUNCTION. If the AS clause is left empty then CREATE FUNCTION assumes the C name of the function is the same as the SQL name. For dynamically-loaded C functions, the SQL name of the function must be the same as the C function name, because the AS clause is used to give the path name of the object file containing the C code. In this situation it is best not to try to overload SQL function names. It might work to load a C function that has the same C name as an internal function or another dynamically-loaded function --- or it might not. On some platforms the dynamic loader may botch the load in interesting ways if there is a conflict of C function names. So, even if it works for you today, you might regret overloading names later when you try to run the code somewhere else. To create a simple SQL function: CREATE FUNCTION one() RETURNS int4 AS 'SELECT 1 AS RESULT' LANGUAGE 'sql'; SELECT one() AS answer; answer ------ 1 To create a C function, calling a routine from a user-created shared library. This particular routine calculates a check digit and returns TRUE if the check digit in the function parameters is correct. It is intended for use in a CHECK contraint. CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c'; CREATE TABLE product ( id char(8) PRIMARY KEY, eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}') REFERENCES brandname(ean_prefix), eancode char(6) CHECK (eancode ~ '[0-9]{6}'), CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode)) ); A C function cannot return a set of values. CREATE FUNCTION is a Postgres language extension. 1998-09-09 PSM stands for Persistent Stored Modules. It is a procedural language and it was originally hoped that PSM would be ratified as an official standard by late 1996. As of mid-1998, this has not yet happened, but it is hoped that PSM will eventually become a standard. SQL/PSM CREATE FUNCTION has the following syntax: CREATE FUNCTION name ( [ [ IN | OUT | INOUT ] parm type [, ...] ] ) RETURNS rtype LANGUAGE 'langname' ESPECIFIC routine SQL-statement