CREATE FUNCTION PostgreSQL User's GuidePrevNextCREATE FUNCTIONName CREATE FUNCTION — Defines a new function CREATE FUNCTION name ( [ ftype [, ...] ] ) RETURNS rtype AS path LANGUAGE 'langname' Inputs name The name of a function to create. ftype The data type of function arguments. rtype The return data type. path May be either an SQL-query or an absolute path to an object file. langname may be 'C', 'sql', 'internal' or 'plname', where 'plname' is the name of a created procedural language. See CREATE LANGUAGE for details. Outputs CREATE This is returned if the command completes successfully. Description CREATE FUNCTION allows a Postgres user to register a function with a database. Subsequently, this user is treated as the owner of the function. Notes Refer to the chapter on functions in the PostgreSQL Programmer's Guide for further information. Use DROP FUNCTION to drop user-defined functions. Usage 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)) ); Bugs A C function cannot return a set of values. Compatibility CREATE FUNCTION is a Postgres language extension. SQL/PSM Note: 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 PrevHomeNextCREATE DATABASEUpCREATE INDEX