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