Unsupported versions: 7.0 / 6.5 / 6.4

Historical version. PostgreSQL 6.4 has reached end of support. See the current manual.

CREATE FUNCTION

Name

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

Report a documentation issue

Read the upstream documentation. For corrections, first check the current manual.